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 -- 相差固定日期的为一组统计

 

  • 2
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

朝闻道-夕死可矣

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值