mySQL三个列组成唯一值查询 sql

forwy 发布于 2016/10/25 19:20
阅读 1K+
收藏 1

有个 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);




加载中
0
hzajie
hzajie

晕啊,需要使用group by子句.

forwy
forwy
对啊,最近都蒙了,感觉大限将至
0
织梦之魂
织梦之魂

引用来自“hzajie”的评论

晕啊,需要使用group by子句.

一时懵逼很正常
0
今夜吴眠

兄弟 你很幸运,我去年写过非常类似的SQL。

如下:

select * from `temp` where (SERVICE_COLLECT_DAY,SERVICE_ID,NODE_ID,APP_ID ,LAST_REQUEST_COUNT) in(
select SERVICE_COLLECT_DAY,SERVICE_ID,NODE_ID,APP_ID ,MAX(LAST_REQUEST_COUNT) from `temp` group by SERVICE_COLLECT_DAY,SERVICE_ID,NODE_ID,APP_ID 
)




上面的SQL有个BUG,就是同一service_collect_day, app_id,node_id,service_id时,有多个最大的LAST_REQUEST_COUNT时,会查询多行,解决办法就是,将上面的SQL再次嵌套,再次groupby

forwy
forwy
谢谢
0
0
o
oneflower
group by app_id,node_id,service_id order by service_collect_day desc;
app_id,node_id,service_id可以考虑加上索引

参考自:MySQL如何优化GROUP BY

0
M
Mr_Cxy
SELECT b.`APP_COLLECT_ID`,b.SERVICE_COLLECT_DAY,b.SERVICE_ID,NODE_ID,b.`APP_ID`,b.`LAST_REQUEST_TIME`,b.`LAST_REQUEST_COUNT`
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
返回顶部
顶部