SQL强化:将相同的或连续的时间段合并

问题描述:有一张签到表,需要将连续或相同的时间段合并,即把多条记录查询合并成一条连续的时间段记录。

数据表如下:

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;
结果如图:

SQL强化:将相同的或连续的时间段合并

SQL强化:将相同的或连续的时间段合并,布布扣,bubuko.com

SQL强化:将相同的或连续的时间段合并

上一篇:oracle安装常见问题-


下一篇:mysql日期和字符相互转换