专家网络公司排名_西安十大广告设计公司_企业管理培训免费课程_免费广告发布平台app

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() 函数

结果：

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