MySQL三个列组成唯一值查询_开源中国问题练习_20161026

问题地址：https://www.oschina.net/question/2923955_2202674

按 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);

#SQL如下

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