這篇文章主要介紹了Oracle排名函式(Rank)實體詳解,需要的朋友可以參考下
--已知：兩種排名方式(磁區和不磁區):使用和不使用partition
--兩種計算方式(連續,不連續),對應函式：dense_rank,rank
·查詢原始資料：學號,姓名,科目名,成績
select * from t_score
| S_ID | S_NAME | SUB_NAME | SCORE |
| 1 | 張三 | 語文 | 80.00 |
| 2 | 李四 | 數學 | 80.00 |
| 1 | 張三 | 數學 | 0.00 |
| 2 | 李四 | 語文 | 50.00 |
| 3 | 張三豐 | 語文 | 10.00 |
| 3 | 張三豐 | 數學 | |
| 3 | 張三豐 | 體育 | 120.00 |
| 4 | 楊過 | JAVA | 90.00 |
| 5 | mike | c++ | 80.00 |
| 3 | 張三豐 | Oracle | 0.00 |
| 4 | 楊過 | Oracle | 77.00 |
| 2 | 李四 | Oracle | 77.00 |
·查詢各學生科目為Oracle排名(簡單排名)select sc.s_id,sc.s_name,sub_name,sc.score,rank() over (order by score desc) 名次from t_score scwhere sub_name='Oracle'
| S_ID | S_NAME | SUB_NAME | SCORE | 名次 |
| 4 | 楊過 | Oracle | 77.00 | 1 |
| 2 | 李四 | Oracle | 77.00 | 1 |
| 3 | 張三豐 | Oracle | 0.00 | 3 |
對比：rank()與dense_rank()：非連續排名與連續排名(都是簡單排名) select sc.s_id,sc.s_name,sub_name,sc.score,dense_rank() over (order by score desc) 名次from t_score scwhere sub_name='Oracle'
| S_ID | S_NAME | SUB_NAME | SCORE | 名次 |
| 4 | 楊過 | Oracle | 77.00 | 1 |
| 2 | 李四 | Oracle | 77.00 | 1 |
| 3 | 張三豐 | Oracle | 0.00 | 2 |
·查詢各學生各科排名(磁區排名)select sc.s_id,sc.s_name,sub_name,sc.score,rank() over(partition by sub_name order by score desc) 名次from t_score sc
| S_ID | S_NAME | SUB_NAME | SCORE | 名次 |
| 4 | 楊過 | JAVA | 90.00 | 1 |
| 4 | 楊過 | Oracle | 77.00 | 1 |
| 2 | 李四 | Oracle | 77.00 | 1 |
| 3 | 張三豐 | Oracle | 0.00 | 3 |
| 5 | mike | c++ | 80.00 | 1 |
| 3 | 張三豐 | 數學 | 1 | |
| 2 | 李四 | 數學 | 80.00 | 2 |
| 1 | 張三 | 數學 | 0.00 | 3 |
| 3 | 張三豐 | 體育 | 120.00 | 1 |
| 1 | 張三 | 語文 | 80.00 | 1 |
| 2 | 李四 | 語文 | 50.00 | 2 |
| 3 | 張三豐 | 語文 | 10.00 | 3 |
·查詢各科前2名(磁區排名)
·類似：新聞表,求欄目點擊率在前3位的新聞，
商品表,求各類別銷售額在前10位的商品，
select * from (select sc.s_id,sc.s_name,sub_name,sc.score,dense_rank() over(partition by sub_name order by score desc) 名次from t_score sc) xwhere x.名次<=2
| S_ID | S_NAME | SUB_NAME | SCORE | 名次 |
| 4 | 楊過 | JAVA | 90.00 | 1 |
| 4 | 楊過 | Oracle | 77.00 | 1 |
| 2 | 李四 | Oracle | 77.00 | 1 |
| 3 | 張三豐 | Oracle | 0.00 | 2 |
| 5 | mike | c++ | 80.00 | 1 |
| 3 | 張三豐 | 數學 | 1 | |
| 2 | 李四 | 數學 | 80.00 | 2 |
| 3 | 張三豐 | 體育 | 120.00 | 1 |
| 1 | 張三 | 語文 | 80.00 | 1 |
| 2 | 李四 | 語文 | 50.00 | 2 |
·查詢各同學總分select s_id,s_name,sum(score) sum_score from t_scoregroup by s_id,s_name
| S_ID | S_NAME | SUM_SCORE |
| 1 | 張三 | 80.00 |
| 2 | 李四 | 207.00 |
| 3 | 張三豐 | 130.00 |
| 4 | 楊過 | 167.00 |
| 5 | mike | 80.00 |
·根據總分查詢各同學名次select x.*,rank() over (order by sum_score desc) 名次from (select s_id,s_name,sum(score) sum_score from t_scoregroup by s_id,s_name ) x
| S_ID | S_NAME | SUM_SCORE | 名次 |
| 2 | 李四 | 207.00 | 1 |
| 4 | 楊過 | 167.00 | 2 |
| 3 | 張三豐 | 130.00 | 3 |
| 1 | 張三 | 80.00 | 4 |
| 5 | mike | 80.00 | 4 |
語法：rank() over (order by 排序欄位 順序)rank() over (partition by 分組欄位 order by 排序欄位 順序) 1.順序：asc|desc 名次與業務相關：示例：找求優秀學員：成績：降序 遲到次數：升序2.磁區欄位：根據什么欄位進行磁區， 問題：磁區與分組有什么區別?·磁區只是將原始資料進行名次排列(記錄數不變),·分組是對原始資料進行聚合統計(記錄數變少,每組回傳一條),注意：聚合，
腳本：
create table t_score( autoid number primary key, s_id number(3), s_name char(8) not null, sub_name varchar2(20), score number(10,2));insert into t_score (autoid, s_id, s_name, sub_name, score)values (8, 1, '張三', '語文', 80);insert into t_score (autoid, s_id, s_name, sub_name, score)values (9, 2, '李四', '數學', 80);insert into t_score (autoid, s_id, s_name, sub_name, score)values (10, 1, '張三', '數學', 0);insert into t_score (autoid, s_id, s_name, sub_name, score)values (11, 2, '李四', '語文', 50);insert into t_score (autoid, s_id, s_name, sub_name, score)values (12, 3, '張三豐', '語文', 10);insert into t_score (autoid, s_id, s_name, sub_name, score)values (13, 3, '張三豐', '數學', null);insert into t_score (autoid, s_id, s_name, sub_name, score)values (14, 3, '張三豐', '體育', 120);insert into t_score (autoid, s_id, s_name, sub_name, score)values (15, 4, '楊過', 'java', 90);insert into t_score (autoid, s_id, s_name, sub_name, score)values (16, 5, 'mike', 'c++', 80);insert into t_score (autoid, s_id, s_name, sub_name, score)values (3, 3, '張三豐', 'oracle', 0);insert into t_score (autoid, s_id, s_name, sub_name, score)values (4, 4, '楊過', 'oracle', 77);insert into t_score (autoid, s_id, s_name, sub_name, score)values (17, 2, '李四', 'oracle', 77);commit;
ending...
轉載：https://www.jb51.net/article/51627.htm
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/36956.html
標籤：Oracle