有个 sql 又不会写了
按 service_collect_day分类以后,按 app_id,node_id,service_id 三个都相同分类,然后取这些里面的 last_request_count 最大值的那一行。
结果也就是这样的
各位大神前辈,求帮助
附:表结构及数据
CREATE TABLE `temp` (
`APP_COLLECT_ID` int(11) NOT NULL AUTO_INCREMENT,
`SERVICE_COLLECT_DAY` datetime DEFAULT NULL,
`SERVICE_ID` int(11) NOT NULL,
`NODE_ID` int(11) DEFAULT NULL,
`APP_ID` int(11) DEFAULT NULL,
`LAST_REQUEST_TIME` datetime DEFAULT NULL,
`LAST_REQUEST_COUNT` int(11) DEFAULT '0',
`LAST_REQUEST_RESPONSE` int(11) DEFAULT '0',
`LAST_REQUEST_RESPONSE_MAX` int(11) DEFAULT '0',
`LAST_REQUEST_RESPONSE_MIN` int(11) DEFAULT '0',
`LAST_REQUEST_ERROR` int(11) DEFAULT '0',
`STATUS` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
`CREATION_DATE` datetime DEFAULT NULL,
`CREATED_BY` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`LAST_UPDATE_DATE` datetime(6) DEFAULT NULL,
`LAST_UPDATED_BY` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`SIDE` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`APP_COLLECT_ID`)
);
INSERT INTO `temp` VALUES ('1', '2016-09-23 00:00:00', '518', '11782', '9', '2016-09-23 08:00:00', '2', '0', '0', '0', '1', 'T', '2016-09-23 17:04:15', null, '2016-09-23 17:06:50.000000', null, 'C');
INSERT INTO `temp` VALUES ('2', '2016-09-23 00:00:00', '518', '11782', '9', '2016-09-23 08:00:00', '222', '0', '0', '0', '1', 'T', '2016-09-23 17:04:15', null, '2016-09-23 17:06:50.000000', null, 'C');
INSERT INTO `temp` VALUES ('3', '2016-09-23 00:00:00', '518', '11782', '9', '2016-09-23 08:00:00', '2222', '0', '0', '0', '1', 'T', '2016-09-23 17:04:15', null, '2016-09-23 17:06:50.000000', null, 'C');
INSERT INTO `temp` VALUES ('4', '2016-09-23 00:00:00', '518', '11782', '9', '2016-09-23 08:00:00', null, '0', '0', '0', '1', 'T', '2016-09-23 17:04:15', null, '2016-09-23 17:06:50.000000', null, 'C');
INSERT INTO `temp` VALUES ('5', '2016-09-24 00:00:00', '519', '11782', '9', '2016-09-23 08:00:00', '1', '0', '0', '0', '1', 'T', '2016-09-23 17:04:15', null, '2016-09-23 17:06:50.000000', null, 'C');
INSERT INTO `temp` VALUES ('6', '2016-09-24 00:00:00', '519', '11782', '9', '2016-09-23 08:00:00', '2', '0', '0', '0', '1', 'T', '2016-09-23 17:04:15', null, '2016-09-23 17:06:50.000000', null, 'C');
INSERT INTO `temp` VALUES ('7', '2016-09-24 00:00:00', '519', '11782', '9', '2016-09-23 08:00:00', '3', '0', '0', '0', '1', 'T', '2016-09-23 17:04:15', null, '2016-09-23 17:06:50.000000', null, 'C');
INSERT INTO `temp` VALUES ('8', '2016-09-23 00:00:00', '517', '11782', '9', null, '212', '0', '0', '0', '0', null, null, null, null, null, null);
引用来自“hzajie”的评论
晕啊,需要使用group by子句.
兄弟 你很幸运,我去年写过非常类似的SQL。
如下:
上面的SQL有个BUG,就是同一service_collect_day, app_id,node_id,service_id时,有多个最大的LAST_REQUEST_COUNT时,会查询多行,解决办法就是,将上面的SQL再次嵌套,再次groupby
app_id,node_id,service_id可以考虑加上索引
参考自:MySQL如何优化GROUP BY
FROM (
SELECT a.`APP_COLLECT_ID`,a.`SERVICE_COLLECT_DAY`,a.`SERVICE_ID`,a.`NODE_ID`,a.`APP_ID`,`LAST_REQUEST_TIME`,a.`LAST_REQUEST_COUNT`
FROM `temp` AS a
GROUP BY a.`SERVICE_COLLECT_DAY`,a.`SERVICE_ID`,a.`NODE_ID`,a.`APP_ID`,a.`LAST_REQUEST_COUNT`
ORDER BY a.`SERVICE_COLLECT_DAY`,a.`LAST_REQUEST_COUNT`DESC
) AS b
GROUP BY b.SERVICE_COLLECT_DAY,`LAST_REQUEST_TIME`
ORDER BY b.SERVICE_COLLECT_DAY,b.`LAST_REQUEST_COUNT`DESC