計算名次 (Row_number / Rank / Dense_rank)

2012-10-22

31527
0
DB(Oracle)
2012-11-29

計算名次

Grade_table

SUBJECT	STUDENT_ID	GRADE
Chemistry	1	60
Chemistry	2	65
Mathematics	1	80
Mathematics	2	85
Mathematics	3	90
Mathematics	4	95
Mathematics	5	100
Physics	1	85
Physics	2	90
Geography	1	95

方法一:

(Select SUBJECT, STUDENT_ID, GRADE, ROWNUM as RN From

           (Select SUBJECT,STUDENT_ID, GRADE From Grade_table

            Where SUBJECT = ‘Mathematics’

            Order by GRADE Desc

           )

      Where ROWNUM < 4 --取前三名

      Order by GRADE Desc

     )

Where RN = 3 --只列第三名

Result

SUBJECT	STUDENT_ID	GRADE	RN
Mathematics	3	90	3

方法二:

(Select SUBJECT, STUDENT_ID, GRADE,

RANK() OVER(PARTITION BY SUBJECT Order by GRADE Desc) as RANK

From Grade_table

      Where SUBJECT = ‘Mathematics’

     )

Where RANK < 4  --列出前三名

Result

SUBJECT	STUDENT_ID	GRADE	RN
Mathematics	5	100	1
Mathematics	4	95	2
Mathematics	3	90	3

Row_number, Rank 與 Dense_rank 差別

     , RANK()       OVER(ORDER BY grade DESC) RANK
     , DENSE_RANK() OVER(ORDER BY grade DESC) DENSE_RANK
     , STUDENT_ID
     , SUBJECT
     , GRADE
  FROM GRADE_TABLE;

Result

ROW_NUM	RANK	DENSE_RANK	STUDENT_ID	SUBJECT	GRADE
1	1	1	5	Mathematics	100
2	2	2	1	Geography	95
3	2	2	4	Mathematics	95
4	4	3	3	Mathematics	90
5	4	3	2	Physics	90
6	6	4	2	Mathematics	85
7	6	4	1	Physics	85
8	8	5	1	Mathematics	80
9	9	6	2	Chemistry	65
10	10	7	1	Chemistry	60

SELECT ROW_NUMBER() OVER(PARTITION BY SUBJECT ORDER BY grade DESC) ROW_NUM
     , RANK()       OVER(PARTITION BY SUBJECT ORDER BY grade DESC) RANK
     , DENSE_RANK() OVER(PARTITION BY SUBJECT ORDER BY grade DESC) DENSE_RANK
     , STUDENT_ID
     , SUBJECT
     , GRADE
  FROM GRADE_TABLE;

Result

ROW_NUM	RANK	DENSE_RANK	STUDENT_ID	SUBJECT	GRADE
1	1	1	5	Mathematics	100
2	2	2	1	Geography	95
3	2	2	4	Mathematics	95
4	4	3	3	Mathematics	90
5	4	3	2	Physics	90
6	6	4	2	Mathematics	85
7	6	4	1	Physics	85
8	8	5	1	Mathematics	80
9	9	6	2	Chemistry	65
10	10	7	1	Chemistry	60

回首頁