sql 合并时间段

 有交叉情况：

-- 虽然这里是以oracle为实例，但是这种方法是可以应用于mysql等其他数据库
CREATE TABLE T1 (START_TIME DATE,END_TIME DATE);
INSERT INTO T1 VALUES(TO_DATE('2016-12-01','YYYY-MM-DD'),TO_DATE('2016-12-05','YYYY-MM-DD'));
INSERT INTO T1 VALUES(TO_DATE('2016-12-03','YYYY-MM-DD'),TO_DATE('2016-12-09','YYYY-MM-DD'));
INSERT INTO T1 VALUES(TO_DATE('2016-12-10','YYYY-MM-DD'),TO_DATE('2016-12-11','YYYY-MM-DD'));


--去除起始时间在区间段内的时间点，注意末尾时间 
SELECT A.START_TIME
  FROM T1 A
  LEFT OUTER JOIN T1 B
    ON A.START_TIME > B.START_TIME
   AND A.START_TIME <= B.END_TIME
 GROUP BY A.START_TIME
HAVING COUNT(B.START_TIME) = 0;

--去除结束时间在区间段内的时间点，注意起始时间 
SELECT A.END_TIME
  FROM T1 A
  LEFT OUTER JOIN T1 B
    ON A.END_TIME >= B.START_TIME
   AND A.END_TIME < B.END_TIME
 GROUP BY A.END_TIME
HAVING COUNT(B.START_TIME) = 0;

SELECT X.START_TIME, MIN(Y.END_TIME)
  FROM (SELECT A.START_TIME
          FROM T1 A
          LEFT OUTER JOIN T1 B
            ON A.START_TIME > B.START_TIME
           AND A.START_TIME <= B.END_TIME
         GROUP BY A.START_TIME
        HAVING COUNT(B.START_TIME) = 0) X
 INNER JOIN (SELECT A.END_TIME
               FROM T1 A
               LEFT OUTER JOIN T1 B
                 ON A.END_TIME >= B.START_TIME
                AND A.END_TIME < B.END_TIME
              GROUP BY A.END_TIME
             HAVING COUNT(B.START_TIME) = 0) Y
    ON X.START_TIME <= Y.END_TIME
 GROUP BY X.START_TIME;

无交叉情况：

create table tmp(datatime date);
insert into tmp values(to_date('2019-01-01','yyyy-mm-dd'));
insert into tmp values(to_date('2019-01-02','yyyy-mm-dd'));
insert into tmp values(to_date('2019-01-04','yyyy-mm-dd'));
insert into tmp values(to_date('2019-01-05','yyyy-mm-dd'));
insert into tmp values(to_date('2019-01-06','yyyy-mm-dd'));
select  min(rq) sday, max(rq) eday, max(id1) - min(id1) + 1 diffday
  from (select datatime - trunc(sysdate) id1,
               (select count(1)
                  from tmp
                 where datatime <= a.datatime
                   ) id2,
               (select max(datatime)
                  from tmp
                 where datatime < a.datatime
                   ) rq2,
               datatime rq
          from tmp a ) tmp
 group by  tmp.id1 - tmp.id2 -- 相差固定日期的为一组统计