MySQL中GROUP BY分组排序获取topN相关的示例分析

这篇文章主要介绍MySQL中GROUP BY分组排序获取topN相关的示例分析，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

MySQL VERSION ： 5.5.45
ENGINE    ： InnoDB

问题描述：
    获取成绩表中每位同学成绩排名前n的记录。

表结构：


表数据：


法一(用户变量)：
①首先我先得到每组排名的所有结果

点击(此处)折叠或打开

1. select @gp_row:=if(@name=name,@gp_row+1,1) as gp_row,@name:=name,id,name,grade from td ,(select @gp_row:=0,@name:='') as temp order by name,grade desc;

得到如下结果：


②在把该结果作为中间表，查询top N的数据

点击(此处)折叠或打开

1. select id ,name ,grade from (select @gp_row:=if(@name=name,@gp_row+1,1) as gp_row,@name:=name,id,name,grade from td ,(select @gp_row:=0,@name:='') as temp order by name,grade desc) as tb_gp_rank where gp_row<=2;

得到结果如下：


③查看执行计划

我们可以看到，对td使用了一个全表扫面(和索引字段相关和select，where字段相关等)，并且用到了using filesort，当表记录数过多的时候效率肯定不高，这也没办法，数据无序以及没有相关索引以及select字段还有一些关系，但是这种查询方式基本可以满足这一类型的基本需求。

法二(union)：
①在知道分组字段的情况下，可以使用union合并各组结果集

点击(此处)折叠或打开

1. (select id ,name ,grade from td where name='tab' order by grade desc limit 2) union (select id ,name ,grade from td where name='lily' order by grade desc limit 2);

②查看执行计划

可以发现，在此种表结构下，union操作会扫n次全表(和索引字段相关)，即多少个union结果集就有多少次。这种方法还需知道需要分组排序的具体的字段值，使用上有限制。

法三(子查询):
①子查询的方式适合选择并列top N的情况

点击(此处)折叠或打开

1. select a.* from td a where (select count(*) from td where td.name=a.name and td.grade>a.grade)<2 order by name,grade desc;

此时在插入一条数据 insert into td select null,'tab',76;
再次查询可得结果如下

也就是说在排序之后最后一条数据如果有重复的则都会作为结果集返回，即第一次查询的结果，tab 成绩为44位于第二名的数据有两条。

法四(join连接)：即把子查询转换为join连接，这里就不在测试。

以上是“MySQL中GROUP BY分组排序获取topN相关的示例分析”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注亿速云行业资讯频道！