1.业务需求

学校希望查看每个班级每门课的前三名学生，显示以下信息

  • 班级
  • 学生姓名
  • 课程名称
  • 成绩
  • 排名
    要求:
  • 按成绩降序排名
  • 每个班每门课独立排名

2.SQL思路

  1. 我们需要Students+Courses+Enrollments三张表
  2. 按班级+课程分组进行排序->用窗口函数ROW_NUMBER()
  3. 筛选前3名->WHERE rank<=3

3.SQL查询示例(使用ROW_NUMBER)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT*
FROM(
    SELECT
        class,
        name AS student_name,
        course_name,
        score,
        ROW_NUMBER() OVER (PARTITION BY class,course_name ORDER BY score DESC )AS rank
    FROM
        Enrollments e
    JOIN
        Students s ON e.student_id = s.student_id
    JOIN
        Courses c ON e.course_id = c course_id
) ranked
WHERE rank <= 3;

说明:

  • PARTITION BY class,course_name->每个班级每门课单独排序
  • ORDER BY score DESC ->只保留前三名

4.查询结果示例

class student_name course_name score rank
一班 李四 数学 95.0 1
二班 张三 数学 85.0 2
三班 王五 数学 80.0 3
四班 赵六 英语 88.0 1
五班 王五 物理 92.0 1

5.扩展练习

1.使用RANK()DENSE_RANK()

  • RANK()遇到并列成绩会跳号
  • DENSE_RANK()遇到并列成绩不跳号
  • 练习比较ROW_NUMBER，RANK,DENSE_RANK的差异
    2.每门课前5名学生
  • 修改WHERE rank <=5
    3.跨班级排名
  • 去掉PARTITION BY class->所有班级一起排名