一、需求抽象
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同样适用。