有交叉情况:
-- 虽然这里是以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 -- 相差固定日期的为一组统计