SQL实现为分组内添加排名字段，查询分组内前三名

问题分析

现有需求是对某张表格依据某字段进行分组，并在每组内依据另一字段进行排序。例如：

学生成绩表

id	name	class	grade
1	张三	A	85
2	李四	A	82
3	王五	B	79
4	小明	A	90
5	小白	B	88
6	小黄	A	66

需要对学生成绩表中数据进行处理，先按照班级进行分组，再按照每个班级内部的成绩进行排名。

并且添加新的排名字段，为每名同学添加排名信息。

方案分析

简单排序

关于分组再排序的查询问题一种最简单的方法就是，通过ORDER BY的特性达到伪分组的效果

SELECT * FROM student 
        ORDER BY class, grade DESC; 

先按照班级进行排序，字段相同的按照grade进行排序。但这种写法丢失了分组的概念。

引入窗口函数添加排名

要解决提出的需求中，添加排名字段信息，依据班级的不同，排名字段需要从1开始，每条记录增加1。可以引用窗口函数来实现这个功能。

SQL的窗口函数是一种用于计算结果集中特定窗口（一组行）的函数。窗口函数可以在查询结果中执行聚合、排序和分析操作，而无需改变查询结果的行数。它们能够在结果集中根据指定的窗口范围计算值，例如在分组内进行排序、计算累计总和或平均值等。窗口函数通常与OVER子句一起使用，用于定义窗口的大小和排序规则。这使得在查询中可以灵活地进行复杂的分析和汇总操作，而无需使用传统的聚合函数或子查询。

SQL如下

SELECT 
    *, 
    row_number() OVER (PARTITION BY class ORDER BY grade DESC) AS row_num
FROM student;

该条查询语句里的窗口函数，会查询以class分组，grade排序的记录，并且返回每个分组内的行号。即满足排行的效果。

id	name	class	grade	row_num
4	小明	A	90	1
1	张三	A	85	2
2	李四	A	82	3
6	小黄	A	66	4
5	小白	B	88	1
3	王五	B	79	2

利用row_num字段的信息，就可以为表格添加关于班级分组的排行字段了。

UPDATE student s
    LEFT JOIN (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY class ORDER BY grade) AS row_num
        FROM student ) t
    ON s.id = t.id
SET s.sort_order = t.row_num
;

将更新表操作连接查询到的分组有序表，并且利用row_num字段进行赋值。这样表格的修改就完成了。

查询前三条数据

查询班级内前三的同学的信息，可以使用in子句来解决

SELECT * FROM student as s1
    WHERE grade IN (
        SELECT t.* FROM (
            SELECT DISTINCT grade 
                FROM student as s2
                    WHERE s1.class=s2.class
                        ORDER BY grade DESC
                            LIMIT 3
            ) AS t
        )
;

其中第三行多余的嵌套子查询，是用于解决MySQL不支持WHERE IN 子句嵌套LIMIT查询的。将内层的子查询进行一层嵌套，且不做改动的查询，从而消除LIMIT的影响。在分析时可将其忽略掉。