排名或顺序的函数 ROW_NUMBER/RANK/DENSE_RANK/NTILE

排名或顺序的函数 ROW_NUMBER/RANK/DENSE_RANK/NTILE

测试表/数据
CREATE TABLE test_course (
  student_name  VARCHAR(10),  -- 学生
  course_name   VARCHAR(10),  -- 课程
  grade         INT           -- 成绩
);

INSERT INTO test_course VALUES('甲', '语文', 95);
INSERT INTO test_course VALUES('乙', '语文', 85);
INSERT INTO test_course VALUES('丙', '语文', 75);
INSERT INTO test_course VALUES('丁', '语文', 65);
INSERT INTO test_course VALUES('戊', '语文', 55);
INSERT INTO test_course VALUES('己', '语文', 50);
INSERT INTO test_course VALUES('庚', '语文', 60);
INSERT INTO test_course VALUES('辛', '语文', 70);
INSERT INTO test_course VALUES('壬', '语文', 80);
INSERT INTO test_course VALUES('奎', '语文', 90);

INSERT INTO test_course VALUES('甲', '数学', 90);
INSERT INTO test_course VALUES('乙', '数学', 80);
INSERT INTO test_course VALUES('丙', '数学', 70);
INSERT INTO test_course VALUES('丁', '数学', 60);
INSERT INTO test_course VALUES('戊', '数学', 50);
INSERT INTO test_course VALUES('己', '数学', 50);
INSERT INTO test_course VALUES('庚', '数学', 60);
INSERT INTO test_course VALUES('辛', '数学', 70);
INSERT INTO test_course VALUES('壬', '数学', 85);
INSERT INTO test_course VALUES('奎', '数学', 95);

 

ROW_NUMBER 顺序编号

按照分数 编号 从高到底
SELECT
  ROW_NUMBER() OVER( ORDER BY SUM(grade) DESC) AS NO,
  student_name,
  SUM(grade) AS AllGrade
FROM
  test_course
GROUP BY
  student_name
ORDER BY
  SUM(grade) DESC

1 甲 185
2 奎 185
3 壬 165
4 乙 165
5 丙 145
6 辛 140
7 丁 125
8 庚 120
9 戊 105
10 己 100

按照分数 编号 从高到底（区分 课程）

SELECT
  ROW_NUMBER() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO,
  *
FROM
  test_course
ORDER BY
  course_name,
  grade DESC

1 奎 数学 95
2 甲 数学 90
3 壬 数学 85
4 乙 数学 80
5 丙 数学 70
6 辛 数学 70
7 庚 数学 60
8 丁 数学 60
9 戊 数学 50
10 己 数学 50
1 甲 语文 95
2 奎 语文 90
3 乙 语文 85
4 壬 语文 80
5 丙 语文 75
6 辛 语文 70
7 丁 语文 65
8 庚 语文 60
9 戊 语文 55
10 己 语文 50

 

RANK 排名不连续

按照分数 排名 从高到底
SELECT
  RANK() OVER( ORDER BY SUM(grade) DESC) AS NO,
  student_name,
  SUM(grade) AS AllGrade
FROM
  test_course
GROUP BY
  student_name
ORDER BY
  SUM(grade) DESC

1 甲 185
1 奎 185
3 壬 165
3 乙 165
5 丙 145
6 辛 140
7 丁 125
8 庚 120
9 戊 105
10 己 100

按照分数 排名 从高到底（区分 课程）

SELECT
  RANK() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO,
  *
FROM
  test_course
ORDER BY
  course_name,
  grade DESC

1 奎 数学 95
2 甲 数学 90
3 壬 数学 85
4 乙 数学 80
5 丙 数学 70
5 辛 数学 70
7 庚 数学 60
7 丁 数学 60
9 戊 数学 50
9 己 数学 50
1 甲 语文 95
2 奎 语文 90
3 乙 语文 85
4 壬 语文 80
5 丙 语文 75
6 辛 语文 70
7 丁 语文 65
8 庚 语文 60
9 戊 语文 55
10 己 语文 50

 

DENSE_RANK 排名连续

按照分数 排名 从高到底

SELECT
  DENSE_RANK() OVER( ORDER BY SUM(grade) DESC) AS NO,
  student_name,
  SUM(grade) AS AllGrade
FROM
  test_course
GROUP BY
  student_name
ORDER BY
  SUM(grade) DESC

1 甲 185
1 奎 185
2 壬 165
2 乙 165
3 丙 145
4 辛 140
5 丁 125
6 庚 120
7 戊 105
8 己 100

按照分数 排名 从高到底（区分 课程）
SELECT
  DENSE_RANK() OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO,
  *
FROM
  test_course
ORDER BY
  course_name,
  grade DESC

1 奎 数学 95
2 甲 数学 90
3 壬 数学 85
4 乙 数学 80
5 丙 数学 70
5 辛 数学 70
6 庚 数学 60
6 丁 数学 60
7 戊 数学 50
7 己 数学 50
1 甲 语文 95
2 奎 语文 90
3 乙 语文 85
4 壬 语文 80
5 丙 语文 75
6 辛 语文 70
7 丁 语文 65
8 庚 语文 60
9 戊 语文 55
10 己 语文 50

 

NTILE 分组

按照分数 划分5个区间 从高到底
SELECT
  NTILE(5) OVER( ORDER BY SUM(grade) DESC) AS NO,
  student_name,
  SUM(grade) AS AllGrade
FROM
  test_course
GROUP BY
  student_name
ORDER BY
  SUM(grade) DESC

1 甲 185
1 奎 185
2 壬 165
2 乙 165
3 丙 145
3 辛 140
4 丁 125
4 庚 120
5 戊 105
5 己 100

按照分数 划分区间 从高到底（区分 课程）
SELECT
  NTILE(5) OVER(PARTITION BY course_name ORDER BY grade DESC) AS NO,
  *
FROM
  test_course
ORDER BY
  course_name,
  grade DESC

1 奎 数学 95
1 甲 数学 90
2 壬 数学 85
2 乙 数学 80
3 丙 数学 70
3 辛 数学 70
4 庚 数学 60
4 丁 数学 60
5 戊 数学 50
5 己 数学 50
1 甲 语文 95
1 奎 语文 90
2 乙 语文 85
2 壬 语文 80
3 丙 语文 75
3 辛 语文 70
4 丁 语文 65
4 庚 语文 60
5 戊 语文 55
5 己 语文 50