同類型的資料做分類群組排序時,可用函數dense_rank()、row_number()、rank()

有時候我們想要將同類型的資料做分類群組排序時,可用函數dense_rank()、row_number()、rank()

例如要抓取每個班級數學成績前10名的學生 ,student :學生資料 ;score_info :數學成績資料

--情況1(dense_rank()):如果有相同成績,則序列都一樣,如A、B同為100分,則序列都是1
SELECT student_id "學號" , student_name "學生姓名" , class_id "班級編號" ,
    FROM(
              SELECT s1.student_id , s1. student_name, s1.class_id ,
                          --先將班級(s1.class_id)分為不同群組,再依成績(s2.score)排序,並在每一個群組內給予1到N的序列
                          dense_rank() over(PARTITION BY s1 . class_id ORDER BY s2. score DESC ) rank_num
                  FROM student s1,score_info s2
                  WHERE s1.student_id = s2 . student_id)
    WHERE rank_num < 11;
  

--情況2( row_number()):不論是否有相同成績,則序列一直編下去,如A、B同為100分,則A為1、B為2
SELECT student_id "學號" , student_name "學生姓名" , class_id "班級編號" ,
    FROM(
              SELECT s1.student_id , s1. student_name, s1.class_id ,
                         -先將班級(s1.class_id)分為不同群組,再依成績(s2.score)排序,並在每一個群組內給予1到N的序列
                         row_number() over(PARTITION BY s1 . class_id ORDER BY s2. score DESC ) rank_num
                  FROM student s1,score_info s2
                  WHERE s1.student_id = s2 . student_id)
    WHERE rank_num < 11;
   
--情況3(rank()):序列一直因為同分數而跳號,如A、B同為100分,C為99分,則A為1、B為1、C為3
SELECT student_id "學號" , student_name "學生姓名" , class_id "班級編號" ,
    FROM(
              SELECT s1.student_id , s1. student_name, s1.class_id ,
                         -先將班級(s1.class_id)分為不同群組,再依成績(s2.score)排序,並在每一個群組內給予1到N的序列
                         rank() over(PARTITION BY s1 . class_id ORDER BY s2. score DESC ) rank_num
                  FROM student s1,score_info s2
                  WHERE s1.student_id = s2 . student_id)
    WHERE rank_num < 11;