问题描述:有一张签到表,需要将连续或相同的时间段合并,即把多条记录查询合并成一条连续的时间段记录。
数据表如下:
DROP TABLE IF EXISTS `timesheets`; CREATE TABLE `timesheets` ( `task_id` varchar(10) NOT NULL DEFAULT ‘‘, `start_date` date DEFAULT NULL, `end_date` date DEFAULT NULL, PRIMARY KEY (`task_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of timesheets -- ---------------------------- INSERT INTO `timesheets` VALUES (‘1‘, ‘2014-03-01‘, ‘2014-03-03‘); INSERT INTO `timesheets` VALUES (‘10‘, ‘2014-03-17‘, ‘2014-03-17‘); INSERT INTO `timesheets` VALUES (‘2‘, ‘2014-03-02‘, ‘2014-03-04‘); INSERT INTO `timesheets` VALUES (‘3‘, ‘2014-03-04‘, ‘2014-03-05‘); INSERT INTO `timesheets` VALUES (‘4‘, ‘2014-03-06‘, ‘2014-03-09‘); INSERT INTO `timesheets` VALUES (‘5‘, ‘2014-03-09‘, ‘2014-03-09‘); INSERT INTO `timesheets` VALUES (‘6‘, ‘2014-03-09‘, ‘2014-03-09‘); INSERT INTO `timesheets` VALUES (‘7‘, ‘2014-03-12‘, ‘2014-03-15‘); INSERT INTO `timesheets` VALUES (‘8‘, ‘2014-03-13‘, ‘2014-03-14‘); INSERT INTO `timesheets` VALUES (‘9‘, ‘2014-03-14‘, ‘2014-03-14‘);
解答:
解法一:
首先需要明确,查询的结果肯定是通过表自身的连接得到的,因为查询的两个字段来自不同的记录行,不妨分组得到可能的记录再筛选:
SELECT a.start_date,b.end_date FROM timesheets a,timesheets b ,timesheets c WHERE a.end_date<=b.end_date GROUP BY a.start_date,b.end_date;
哪些记录需要排除呢,结果字段在原表同一条记录的两值之间的记录都得干掉,起点和起点可相同,终点和终点可相同:
SELECT a.start_date,b.end_date FROM timesheets a,timesheets b ,timesheets c WHERE a.end_date<=b.end_date GROUP BY a.start_date,b.end_date HAVING MAX(CASE WHEN (a.start_date>c.start_date and a.start_date<=c.end_date) OR (b.end_date>=c.start_date and b.end_date<c.end_date) then 1 ELSE 0 END) =0 ;
最后,从这个结果集中分组得到最终的结果,起始时间和组内最小的终止时间:
SELECT d.start_date,MIN(d.end_date) from (SELECT a.start_date,b.end_date FROM timesheets a,timesheets b ,timesheets c WHERE a.end_date<=b.end_date GROUP BY a.start_date,b.end_date HAVING MAX(CASE WHEN (a.start_date>c.start_date and a.start_date<=c.end_date) OR (b.end_date>=c.start_date and b.end_date<c.end_date) then 1 ELSE 0 END) =0) d GROUP BY d.start_date;
解法二:
通过左连接分别得到起始时间和终止时间,然后通过内连接合并结果:
#干掉起点时间在某记录起止时间之间的 SELECT a.start_date FROM timesheets a LEFT OUTER JOIN timesheets b ON a.start_date>b.start_date AND a.start_date<=b.end_date GROUP BY a.start_date HAVING COUNT(b.start_date)=0; #干掉终点时间在某记录起止时间之间的 SELECT a.end_date FROM timesheets a LEFT OUTER JOIN timesheets b ON a.end_date>=b.start_date AND a.end_date<b.end_date GROUP BY a.end_date HAVING COUNT(b.start_date)=0; SELECT x.start_date,MIN(y.end_date) FROM (SELECT a.start_date FROM timesheets a LEFT OUTER JOIN timesheets b ON a.start_date>b.start_date AND a.start_date<=b.end_date GROUP BY a.start_date HAVING COUNT(b.start_date)=0) x INNER JOIN (SELECT a.end_date FROM timesheets a LEFT OUTER JOIN timesheets b ON a.end_date>=b.start_date AND a.end_date<b.end_date GROUP BY a.end_date HAVING COUNT(b.start_date)=0) y ON x.start_date<=y.end_date GROUP BY x.start_date;结果如图: