mysql查询数据分组后保留每组前n条数据

最近做项目遇到一个这样需求的sql语句,根据一系列的条件后,分组查询出来的数据是这样的
这里写图片描述
可以看到用户id=4 的有三条数据,用户id=1的有两条数据。
我需求是相同的用户id只取最多两条数据,也就如下图
这里写图片描述
红色箭头的这两行都不需要查询出来,因为用户id=4和用户id=3 都有三条数据了,而我只需要两条。

需求讲的啰嗦,我怕我表达不清楚,以上应该够清楚了。。。不啰嗦了,上代码
先创建测试数据,运行下面sql语句创建表

DROP TABLE IF EXISTS `aaaa`;
CREATE TABLE `aaaa` (
  `EPI_AUTOID` int(10) NOT NULL AUTO_INCREMENT COMMENT '唯一ID',
  `EPI_UIFID` int(10) DEFAULT NULL COMMENT '用户ID',
  `EPI_CNAME` varchar(50) DEFAULT NULL COMMENT '案例名称',
  `EPI_CREMARK` varchar(1500) DEFAULT NULL COMMENT '案例描述',
  `EPI_DSTID` int(10) DEFAULT NULL COMMENT '设计类型ID',
  `EPI_CBRAND` varchar(50) DEFAULT NULL COMMENT '品牌名称',
  `EPI_DCREATEDT` datetime DEFAULT NULL COMMENT '创建时间',
  `EPI_DEDITDT` datetime DEFAULT NULL COMMENT '修改时间',
  `EPI_CIMGURL` varchar(200) DEFAULT NULL COMMENT '文件地址',
  `EPI_ISTATUS` int(1) DEFAULT '1' COMMENT '状态:0-禁用;1-正常;',
  `EPI_CRMK` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`EPI_AUTOID`)
) ENGINE=InnoDB AUTO_INCREMENT=159 DEFAULT CHARSET=utf8 COMMENT='个人案例';

-- ----------------------------
-- Records of aaaa
-- ----------------------------
INSERT INTO `aaaa` VALUES ('48', '2', null, null, '1', null, '2018-04-28 00:00:00', null, '/2018/04/28/15249109974729478.jpg', '1', null);
INSERT INTO `aaaa` VALUES ('49', '4', null, null, '1', null, '2018-05-09 00:00:00', null, '/2018/05/09/15258527668649205.jpg', '0', null);
INSERT INTO `aaaa` VALUES ('50', '4', null, null, '1', null, '2018-05-09 00:00:00', null, '/2018/05/09/15258529511213373.jpg', '0', null);
INSERT INTO `aaaa` VALUES ('51', '4', null, null, '1', null, '2018-05-09 00:00:00', null, '/2018/05/09/15258529902909544.jpg', '0', null);
INSERT INTO `aaaa` VALUES ('52', '4', null, null, '1', null, '2018-05-09 00:00:00', null, '/2018/05/09/15258584160643011.jpg', '0', null);
INSERT INTO `aaaa` VALUES ('53', '4', null, null, '1', null, '2018-05-09 00:00:00', null, '/2018/05/09/15258599145688895.jpg', '0', null);
INSERT INTO `aaaa` VALUES ('54', '4', null, null, '1', null, '2018-05-10 00:00:00', null, '/2018/05/10/15259183085396398.jpg', '0', null);
INSERT INTO `aaaa` VALUES ('55', '4', null, null, '1', null, '2018-05-10 00:00:00', null, '/2018/05/10/15259188493065025.jpg', '0', null);
INSERT INTO `aaaa` VALUES ('56', '4', null, null, '1', null, '2018-05-10 00:00:00', null, '/2018/05/10/15259218864181474.jpg', '0', null);
INSERT INTO `aaaa` VALUES ('57', '4', null, null, '1', null, '2018-05-10 00:00:00', null, '/2018/05/10/1525923932433940.jpg', '0', null);
INSERT INTO `aaaa` VALUES ('58', '4', null, null, '1', null, '2018-05-11 00:00:00', null, '/2018/05/11/15260295069428892.jpg', '0', null);
INSERT INTO `aaaa` VALUES ('59', '1', '这是项目名', '这是项目描述································································', '1', null, '2018-05-15 00:00:00', null, '/2018/05/15/15263550404122435.jpg', '1', null);
INSERT INTO `aaaa` VALUES ('60', '1', null, null, '1', null, '2018-05-15 00:00:00', null, '/2018/05/15/15263550465699855.jpg', '1', null);
INSERT INTO `aaaa` VALUES ('61', '1', null, null, '1', null, '2018-05-15 00:00:00', null, '/2018/05/15/15263550537244329.jpg', '1', null);
INSERT INTO `aaaa` VALUES ('71', '4', 'Q5 上市展', '超豪华展会,特多美女模特', '1', '奥迪', '2018-05-20 00:00:00', '2018-05-20 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('72', '4', 'Q5 上市展', '超豪华展会,特多美女模特', '1', '奥迪', '2018-05-20 00:00:00', '2018-05-20 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('73', '4', 'Q5 上市展', '超豪华展会,特多美女模特', '1', '奥迪', '2018-05-20 00:00:00', '2018-05-20 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('74', '4', '新品上市展会', 'x5上市大型展会', '1', '宝马', '2018-05-20 00:00:00', '2018-05-20 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('75', '4', 'Q5 上市展', '超豪华展会,特多美女模特', '1', '奥迪', '2018-05-20 00:00:00', '2018-05-20 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('76', '4', '百度无人驾驶汽车展', '百度AI无人驾驶汽车展会活动策划', '1', '百度', '2018-05-20 00:00:00', '2018-05-20 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('77', '4', '新品上市展会', 'x5上市大型展会', '1', '宝马', '2018-05-20 00:00:00', '2018-05-20 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('78', '4', 'Q5 上市展', '超豪华展会,特多美女模特', '1', '奥迪', '2018-05-20 00:00:00', '2018-05-20 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('79', '4', '百度无人驾驶汽车展', '百度AI无人驾驶汽车展会活动策划', '1', '百度', '2018-05-20 00:00:00', '2018-05-20 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('80', '4', '百度无人驾驶汽车展', '百度AI无人驾驶汽车展会活动策划', '1', '百度', '2018-05-20 00:00:00', '2018-05-20 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('81', '4', '老板展会', '发的发送到发送到发送到发送到', '1', '老板电器', '2018-05-22 00:00:00', '2018-05-22 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('82', '4', '倒霉展会', '东方闪电发的说法阿萨德', '1', '倒霉', '2018-05-22 00:00:00', '2018-05-22 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('83', '4', '范德萨的 ', ' 发的发的 的范德萨的', '1', '苦逼 地方', '2018-05-22 00:00:00', '2018-05-22 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('84', '4', '尔尔发的的', '是范德萨发送到', '1', '发的方法', '2018-05-22 00:00:00', '2018-05-22 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('85', '4', '辅导费的神色', '发的 爱的所发生的发 ', '1', '事发地点时', '2018-05-22 00:00:00', '2018-05-22 00:00:00', null, '0', null);
INSERT INTO `aaaa` VALUES ('86', '3', '发的发', '发送到发送到 ', '1', '发的撒', '2018-05-22 00:00:00', '2018-05-22 00:00:00', null, '1', null);
INSERT INTO `aaaa` VALUES ('101', '3', '111', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('102', '3', '222', '444', '0', '333', '2018-05-25 00:00:00', null, null, '0', null);
INSERT INTO `aaaa` VALUES ('103', '3', '111', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('107', '3', '1110', '333', '0', '222', '2018-05-25 00:00:00', null, null, '0', null);
INSERT INTO `aaaa` VALUES ('108', '3', '11101', '333', '0', '222', '2018-05-25 00:00:00', null, null, '0', null);
INSERT INTO `aaaa` VALUES ('109', '3', '111012', '333', '0', '222', '2018-05-25 00:00:00', null, null, '0', null);
INSERT INTO `aaaa` VALUES ('110', '3', '1', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('111', '3', '2', '333', '0', '222', '2018-05-25 00:00:00', null, null, '0', null);
INSERT INTO `aaaa` VALUES ('112', '3', '3', '333', '0', '222', '2018-05-25 00:00:00', null, null, '0', null);
INSERT INTO `aaaa` VALUES ('113', '3', '4', '333', '0', '222', '2018-05-25 00:00:00', null, null, '0', null);
INSERT INTO `aaaa` VALUES ('114', '3', '5', '333', '0', '222', '2018-05-25 00:00:00', null, null, '0', null);
INSERT INTO `aaaa` VALUES ('115', '3', '7', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('116', '3', '8', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('117', '3', '9', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('118', '3', '10', '333', '0', '222', '2018-05-25 00:00:00', null, null, '0', null);
INSERT INTO `aaaa` VALUES ('119', '3', '11', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('120', '3', '11', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('121', '3', '11', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('122', '3', '11', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('123', '3', '11', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('124', '3', '11', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('125', '3', '11', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('126', '3', '11', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('127', '3', '11', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('128', '3', '11', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('129', '3', '11', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('130', '3', '11', '333', '0', '222', '2018-05-25 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('131', '4', '你好帅', '反倒是水电费地方胜多负少啊发多少发生违法', '0', '濑尿虾', '2018-05-28 00:00:00', null, null, '0', null);
INSERT INTO `aaaa` VALUES ('132', '4', '你贼帅', '是范德萨发送到78787', '0', '发的方法00', '2018-05-29 00:00:00', null, null, '0', null);
INSERT INTO `aaaa` VALUES ('133', '4', '呵呵', '对方的 反对发', '1', '很好', '2018-05-29 00:00:00', '2018-05-29 00:00:00', null, '1', null);
INSERT INTO `aaaa` VALUES ('134', '4', '反对反对地方', ' 嗯嫩恩那你额额', '1', '超帅的', '2018-05-29 00:00:00', '2018-05-29 00:00:00', null, '1', null);
INSERT INTO `aaaa` VALUES ('135', '4', '哈哈哈哈哈哈', '嘻嘻嘻嘻嘻', '1', '呵呵呵呵', '2018-05-29 00:00:00', '2018-05-29 00:00:00', null, '1', null);
INSERT INTO `aaaa` VALUES ('137', '3', '11', '333', '0', '222', '2018-06-07 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('138', '3', '112', '333', '0', '222', '2018-06-07 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('139', '3', '3', '333', '0', '222', '2018-06-07 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('140', '3', '4', '333', '0', '222', '2018-06-07 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('141', '3', '51', '333', '0', '222', '2018-06-07 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('142', '3', '99-999901', '9999', '0', '999', '2018-06-08 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('143', '3', '1', '1', '0', '1', '2018-06-08 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('144', '3', '2', '2', '0', '2', '2018-06-08 00:00:00', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('145', '3', '2222', '222', '0', '2', '2018-06-08 13:57:41', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('146', '3', '1', '1', '0', '1', '2018-06-08 14:37:14', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('147', '3', '2', '2', '0', '2', '2018-06-08 14:37:24', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('148', '3', '4', '4', '0', '4', '2018-06-08 14:37:39', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('149', '3', '5', '5', '0', '5', '2018-06-08 14:37:57', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('150', '1', '1', '1', '0', '1', '2018-06-20 17:26:57', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('151', '1', '13711764735', '多岁的', '0', '多岁的', '2018-07-21 11:16:19', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('152', '1', 'sdfsdsd', 'dsdsfcdfd5t45t4545rte', '0', 'dsfd12121', '2018-07-21 16:02:42', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('156', '1', '佛挡杀佛第三方', 'fdsf发送的佛挡杀佛第三方', '0', 'fdsf', '2018-07-21 18:15:17', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('157', '3', '丰富的法定', '范德萨范德萨范德萨', '0', '辅导费', '2018-07-24 10:23:25', null, null, '1', null);
INSERT INTO `aaaa` VALUES ('158', '3', 'aaaaaaaaaaaa', 'dsadsadsa', '0', 'dsdsa', '2018-07-24 10:26:37', null, null, '1', null);

查询语句,这个语句就实现了分组后保留每组前三条数据

SELECT EPI_UIFID,EPI_CNAME,new_rank as rank from 
(SELECT EPI_UIFID,EPI_CNAME,
IF(@tmp=EPI_UIFID,@rank:=@rank + 1,@rank:=1) as new_rank,
@tmp:=EPI_UIFID as tmp
FROM aaaa
ORDER BY EPI_UIFID DESC) b
where new_rank <= 3;

参考百度经验:https://jingyan.baidu.com/article/d8072ac48d2730ec94cefd43.html

  • 2
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 5
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值