今天MySQL群里有人发了这个问题:
建立测试数据和样表:
- SET FOREIGN_KEY_CHECKS=0;
- -- ----------------------------
- -- Table structure for `adtimelog`
- -- ----------------------------
- DROP TABLE IF EXISTS `adtimelog`;
- CREATE TABLE `adtimelog` (
- `Aname` varchar(12) CHARACTER SET utf8 DEFAULT NULL,
- `LITime` timestamp NULL DEFAULT NULL,
- `LOTime` timestamp NULL DEFAULT NULL,
- `DTime` timestamp NULL DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
- -- ----------------------------
- -- Records of adtimelog
- -- ----------------------------
- INSERT INTO `adtimelog` VALUES ('甲', '2015-08-13 08:30:00', '2015-08-13 09:30:00', '2015-08-13 00:00:00');
- INSERT INTO `adtimelog` VALUES ('甲', '2015-08-13 09:40:00', '2015-08-13 10:00:00', '2015-08-13 00:00:00');
- INSERT INTO `adtimelog` VALUES ('甲', '2015-08-13 08:30:00', '2015-08-13 11:30:00', '2015-08-13 00:00:00');
- INSERT INTO `adtimelog` VALUES ('丁', '2015-08-13 00:30:00', '2015-08-13 01:30:00', '2015-08-13 00:00:00');
- INSERT INTO `adtimelog` VALUES ('丁', '2015-08-13 02:30:00', '2015-08-13 07:30:00', '2015-08-13 00:00:00');
SQL:
- select '2015-08-13 00:00:00',min(LITime) from ADTimeLog where DTime='2015/8/13'
- UNION ALL
- select distinct a.LOTime,min(b.LITime) from
- (select LITime,LOTime from ADTimeLog where DTime='2015/8/13' order by LITime ) a,
- (select LITime,LOTime from ADTimeLog where DTime='2015/8/13' order by LITime limit 1,99999) b
- where a.LOTime<b.LITime GROUP BY a.LOTime
- UNION ALL
- select max(LOTime),'2015-08-13 23:59:59' from ADTimeLog where DTime='2015/8/13'