MySQL分组后取前几条数据

MySQL分组后取前几条数据

环境：mysql 5.7

数据库中有一个报餐表，我要取各个用餐地址组内用餐最多的前三名数据

表中部分数据是这样的

脚本

INSERT INTO `ordering_meal` VALUES ('百度2号楼', 1, 12, NOW());
INSERT INTO `ordering_meal` VALUES ('百度2号楼', 1, 4, NOW());
INSERT INTO `ordering_meal` VALUES ('百度8号楼', 2, 30, NOW());
INSERT INTO `ordering_meal` VALUES ('百度8号楼', 4, 8, NOW());
INSERT INTO `ordering_meal` VALUES ('新浪1号楼', 3, 19, NOW());
INSERT INTO `ordering_meal` VALUES ('百度2号楼', 2, 20, NOW());
INSERT INTO `ordering_meal` VALUES ('百度2号楼', 4, 29, NOW());
INSERT INTO `ordering_meal` VALUES ('百度2号楼', 2, 24, NOW());
INSERT INTO `ordering_meal` VALUES ('百度2号楼', 3, 14, NOW());
INSERT INTO `ordering_meal` VALUES ('百度8号楼', 4, 14, NOW());
INSERT INTO `ordering_meal` VALUES ('百度8号楼', 2, 14, NOW());
INSERT INTO `ordering_meal` VALUES ('百度8号楼', 2, 16, NOW());
INSERT INTO `ordering_meal` VALUES ('百度8号楼', 4, 36, NOW());
INSERT INTO `ordering_meal` VALUES ('百度8号楼', 2, 12, NOW());
INSERT INTO `ordering_meal` VALUES ('百度8号楼', 2, 2, NOW());
INSERT INTO `ordering_meal` VALUES ('百度8号楼', 2, 8, NOW());
INSERT INTO `ordering_meal` VALUES ('百度8号楼', 2, 17, NOW());
INSERT INTO `ordering_meal` VALUES ('百度2号楼', 3, 27, NOW());
INSERT INTO `ordering_meal` VALUES ('百度8号楼', 4, 23, NOW());
INSERT INTO `ordering_meal` VALUES ('百度2号楼', 4, 3, NOW());
INSERT INTO `ordering_meal` VALUES ('新浪1号楼', 4, 83, NOW());
INSERT INTO `ordering_meal` VALUES ('百度2号楼', 1, 57, NOW());

1.第一种方法

SELECT 
  lounum, fdcount 
FROM 
  ordering_meal a
where 3 > (
	SELECT count(*) 
	FROM ordering_meal b 
	WHERE 
	  b.lounum = a.lounum 
		AND b.fdcount > a.fdcount )
ORDER BY a.lounum, a.fdcount DESC

结果：

这个会把组内所有跟前三名并列的都查出来（自己可以再添加2条数据测试一下）
速度：✿

2.第二种方法

SELECT * 	FROM (
	SELECT a.*, case when lounum = @lounum then @r:=@r+1 else @r:=1 end as r, @lounum:=lounum 
	FROM (
		SELECT lounum, fdcount FROM `ordering_meal` ORDER BY lounum, fdcount DESC) a,
		(select @lounum='', @r=0)tmp) q
WHERE r <= 3

这种方法就是代替mysql 8.0 的row_number() over（）窗口函数的方法，因为mysql5.7没有row_number()函数

结果：

这个方法就是只取组内的前三条，不管后面还有没有相同的

速度：✿✿✿✿✿

3.第三种方法

SELECT * FROM (	
	SELECT a.*, @r:= case when lounum!=@lounum  then 1 when @fdcount=fdcount then @r else @r:=@r+1 end as r,@lounum:=lounum,@fdcount:=fdcount 
		FROM(
			SELECT lounum,fdcount from  `ordering_meal` order by lounum, fdcount desc)a,
			(SELECT @lounum='',@r=0,@fdcount=0)tmp
	) q
where r <= 3

这种方法就是代替mysql 8.0 的dense_rank() over()窗口函数的方法，因为mysql5.7没有dense_rank() 函数

结果：

把前三名并列的都查询出来

速度：✿✿✿✿✿

4.第四种方法

SELECT * FROM (
	SELECT 
		lounum,fdcount, row_number() over(partition by lounum order by fdcount desc) as a FROM `ordering_meal`
		) r 
WHERE a <= 3

结果：

跟方法二的逻辑是一样的，但是可以按照业务场景需求来选择使用rank() ,dense_rank(),row_number()来查询。