SQL 判斷兩個時間段是否有重疊部分-CSDN博客

SELECT COUNT(*) FROM (select t.*,
       (case
         when ((to_date('2016/03/15 19:03', 'yyyy/mm/dd HH24:mi') between
              to_date(t.S_time, 'yyyy/mm/dd HH:mi') and
              to_date(t.e_time, 'yyyy/mm/dd HH24:mi')) 
              AND
              (to_date('2016/03/15 19:10', 'yyyy/mm/dd HH24:mi') between
              to_date(t.S_time, 'yyyy/mm/dd HH:mi') and
              to_date(t.e_time, 'yyyy/mm/dd HH24:mi')))
           OR 
              ((to_date('2016/03/15 19:03', 'yyyy/mm/dd HH24:mi') between
              to_date(t.S_time, 'yyyy/mm/dd HH:mi') and
              to_date(t.e_time, 'yyyy/mm/dd HH24:mi')) 
              AND
              (to_date('2016/03/15 19:10', 'yyyy/mm/dd HH24:mi') >
              to_date(t.e_time, 'yyyy/mm/dd HH24:mi')))
           OR
              ((to_date('2016/03/15 19:03', 'yyyy/mm/dd HH24:mi') <
              to_date(t.e_time, 'yyyy/mm/dd HH24:mi')) 
              AND
              (to_date('2016/03/15 19:10', 'yyyy/mm/dd HH24:mi') between
              to_date(t.S_time, 'yyyy/mm/dd HH:mi') and
              to_date(t.e_time, 'yyyy/mm/dd HH24:mi')))
           OR
              ((to_date('2016/03/15 19:03', 'yyyy/mm/dd HH24:mi')<
              to_date(t.S_time, 'yyyy/mm/dd HH:mi')  
              AND
              (to_date('2016/03/15 19:10', 'yyyy/mm/dd HH24:mi') >
              to_date(t.e_time, 'yyyy/mm/dd HH24:mi'))))
               then
          '1'
         else
          '0'
       end) cc
  from (select (to_char(l.applierdate, 'yyyy/mm/dd') || ' ' || l.starttime) S_time,
               (to_char(l.applierdate, 'yyyy/mm/dd') || ' ' || l.endtime) e_time
          from line l
         where l.applierdate = to_date('2016/03/15', 'yyyy/mm/dd')
           and l.empno = '9409872') t) M
 WHERE M.CC='1'

其中 '2016/03/15'是參數DATE,

'2016/03/15 19:03'為開始時間，

'2016/03/15 19:10'為結束時間，

l.empno =：參數-- '9409872'

共分別為4種情況：

转载于:https://www.cnblogs.com/Shirly-Zhang/p/5302257.html