mysql 每组 topn_Mysql分组取每组TopN条记录

一、需求抽象

1、商品表中包含各个商品的分类和价格；

2、查找商品表中各个分类下价格最高的前N个商品。

3、每个分类最多返回N个，价格一样的取id较小的。

表t_business_product为商品表，category_id1为分类id，price为商品价格，id为商品id。

二、需求细化及分析

A、假设N=1，既查找每个分类下价格最高的商品；

我们先通过一个错误的示例来加深下对“group by”和“order by”的理解。

SELECT * FROM t_business_product GROUP BY category_id1 ORDER BY price;

得到如下结果，明显不是我们想要的。

原因是mysql的执行顺序如下：

写的顺序：select ... from... where.... group by... having... order by..

执行顺序：from... where...group by... having.... select ... order by...

在order by的时候拿到的结果已经是经过分组和筛选的。

group by之后得到的几个分组如下：

select的时候从每个分组中取第一条，结果如下：

order by的时候就是对上面的结果进行排序，就得到了开头那样的结果。

经过上面分析，我们可以在group by之前采用子查询对所有的商品进行一次价格排序，这样group by之后得到的各个分组的第一条就是价格最高的。

SELECT * FROM (SELECT * FROM t_business_product ORDER BY price DESC,id) t  GROUP BY category_id1;

得到如下结果就是我们想要的：

B、N>1的情况；

分析：假设N=2，那么符合条件的那些商品应该满足什么条件呢？既同类商品中价格比它高的不超过1个，也就是<2。那么对于每个商品，我们可以遍历下商品表统计下同类商品价格比它高的有几个，如果<2，我们就把它加入返回结果中。

得到的结果如下，符合我们的预期：

此时如果我们再添加一个category_id1=1且价格跟第二名一样的商品，结果会怎么样呢？

如下，我们看到结果中1类商品返回了3个，不符合我们的要求。

转换思路，先对每类商品按价格从高到低、id从低到高进行排序，再把每个类排序在前N的商品返回。

引入局部变量@category和@rank，其中@category初始化为NULL，@rank初始化为1，作为sno代表组内排序。

解释一下：GROUP BY 里面的category_id1是一定要加上的，起到分组的作用。因为遇到不同的category_id1，@rank就会从1开始计数。分组之后，再根据price的降序、id的升序进行排序，然后遍历各个分组，给每个商品赋上在本组的排序。当每个商品都有了自己在同类中的排序时，就可以根据这个序号来过滤出前N个商品。

结果如下，符合我们的预期：

此时只要动态传入sno的值N，就能返回价格排名前N的商品列表。N=1同样适用。