mysql5.7.20实现取group by 每个分组前几行数据

目录

一、环境

二，数据和要求

三、实现方式

四、实现取group by 每个分组前几行数据测试结果

---

一、环境

mysql5.7.20

二，数据和要求

有如下表和数据：需实现取group by 每个分组前几行数据

其中：

cst_id：客户id

st_id：店铺id

expend_count ：到店支付次数

expend_sum：到店支付总金额数

现需要统计，每个客户最喜欢的店铺（每一个客户消费次数最多的店铺）信息。

比个如，有三个学生，
A学生，去1号店消费90次，2号店5次，三号店5次。
B学生，去1号店消费10次，2号店50次，三号店40次。
C学生，去1号店消费5次，2号店50次，三号店45次。

则A学生，最喜欢1号店，B学生最喜欢2号店，C学生也最喜欢2号店。

以上需求在很多场合会用到，

比如，有个成绩表，想查出每个科目，成绩的前三名，或者后三名；

再比如，查询每个客户购买最多的前两个商品，（是每个客户的，不是全部客户的）

这里就会涉及 group by 分组中，取每个分组的前几行数据。

三、实现方式

Oracle中有row_number() over分析函数，可以比较便捷的实现以上功能，mysql 5.7版本暂时没有提供类似功能，所以只能自己实现

参考DB中某位大佬的写法，实现如下：

SELECT bb.cst_id,bb.st_id,bb.expend_count,bb.expend_sum FROM
(select if(@uid=t.cst_id,@rank:=@rank+1,@rank:=1) as rank,
t.*,
@uid:=t.cst_id AS cst_id_b
from (select @uid:=null,@cid:=null,@rank:=0) r, (SELECT cst_id,st_id,expend_count,expend_sum FROM perfectstore ORDER BY cst_id ASC,expend_count DESC) AS t) AS bb
WHERE bb.rank<3
ORDER BY bb.cst_id,bb.expend_count DESC,bb.st_id;

其中，通过if(@uid=t.cst_id,@rank:=@rank+1,@rank:=1) 条件句来判断变量值与当前cst_id是否相等来，不等说明是新的cst_id，rank就为1，否则就+1

另外，SELECT cst_id,st_id,expend_count,expend_sum FROM perfectstore ORDER BY cst_id ASC,expend_count DESC，先对原有记录进行相应的排序

四、实现取group by 每个分组前几行数据测试结果

最终的结果如下：

第一种：取前两条数据：

第二种：取第一条数据：