mysql 取分组数据的前几名（1）

不念不忘少年蓝@ 2023-05-29 02:40 21阅读 0赞

目前有需求需要取分组数据的前几名，有如下的解决方案来实现

具体数据库如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnppNTI4_size_16_color_FFFFFF_t_70][]

SQL写法:

SELECT
    	* 
    FROM
    	student_grade AS a 
    WHERE
    	( SELECT coun ( * ) FROM student_grade AS b WHERE b.subid = a.subid AND b.grade >= a.grade ) <= 2 
    ORDER BY
    	a.subid,
    	a.grade DESC

结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnppNTI4_size_16_color_FFFFFF_t_70 1][]

理解：

核心思路：要算出某人成绩在第几名，可以转换成：算出他一共比多少人成绩高。比如，第一名的人，就没其它人成绩比他更好。第三名的人，就有两个人成绩比他好。

where语句可以理解为，把表中的每一行记录，都去与给定的where条件作对比，满足的再查出来。也就是有个遍历的过程。

模拟下SQL执行的过程就是，先取出外层a表的第一条记录  
![20181123221728266.png][]

执行where中的子查询：

select count(1) from student\_grade b where b.subId=1 and b.grade>=97

加粗的数据，即从外层a表取出来的。意思是课程1中，有多少个学生的成绩是大于等于97的？97已是最高分了，只有一个。因此满足外层的条件，小于等于2.  
![20181123221728298.png][]

接下来取外层表第二条记录

![20181123221728442.png][]

where子句变成了：

select count(1) from student\_grade b where b.subId=1 and b.grade>=93

意思是课程1中，有多少个学生的成绩是大于等于93的？结果是2，因为97和93分都大于等于93分。因此满足外层的条件，小于等于2.这样就取出了每门课的前两名。

接下来取外层表第三条记录：  
![20181123221728549.png][]

where子句变成了：

select count(1) from student\_grade b where b.subId=1 and b.grade>=92

意思是课程1中，有多少个学生的成绩是大于等于92的？结果是3，因为97、93、92分都大于等于92分。因此不满足外层的条件，小于等于2.

以此类推...

扩展一下：

取每组第一名的记录，只需要把下图红框处改为<=1

取每组成绩最低的，只需把下图黑框处改为b.grade<=a.grade  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnppNTI4_size_16_color_FFFFFF_t_70 2][]

此外需要注意的是，该方法在有相同的多个数据时，会导致相同结果全部被选中，此时可以考虑采用另外的方法：

SELECT
    	s2.stuid,
    	s2.subid,
    	s2.grade 
    FROM
    	(
    	SELECT
    	IF
    		( s1.subid = @subid, @rank := @rank + 1, @rank := 1 ) AS rank,
    		@subid := subid AS tmp_subid,
    		s1.stuid,
    		s1.subid,
    		s1.grade 
    	FROM
    		( SELECT stuid, subid, grade FROM student_grade ORDER BY subid, grade DESC ) s1,
    		( SELECT @subid := NULL, @rank := 1 ) tmp 
    	) s2 
    WHERE
    	s2.rank <= 2

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnppNTI4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20181123221629262.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnppNTI4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnppNTI4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20181123221710211.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnppNTI4,size_16,color_FFFFFF,t_70
[20181123221728266.png]: https://img-blog.csdnimg.cn/20181123221728266.png
[20181123221728298.png]: https://img-blog.csdnimg.cn/20181123221728298.png
[20181123221728442.png]: https://img-blog.csdnimg.cn/20181123221728442.png
[20181123221728549.png]: https://img-blog.csdnimg.cn/20181123221728549.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnppNTI4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20181123223308204.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2p1bnppNTI4,size_16,color_FFFFFF,t_70