【SQL】查询两个时间段是否有交集的情况

//方法一

select *
from house
where roomid = 21 and (
(startTime >= to_Date('2021-08-14 00:00:00','yyyy-MM-dd hh24:mi:ss') AND startTime <= to_Date('2021-08-18 00:00:00','yyyy-MM-dd hh24:mi:ss')) OR
(startTime <= to_Date('2021-08-14 00:00:00','yyyy-MM-dd hh24:mi:ss') AND endTime >= to_Date('2021-08-18 00:00:00','yyyy-MM-dd hh24:mi:ss')) OR
(endTime >= to_Date('2021-08-14 00:00:00','yyyy-MM-dd hh24:mi:ss') AND endTime <= to_Date('2021-08-18 00:00:00','yyyy-MM-dd hh24:mi:ss'))
)

 

//方法二

select *
from house
where roomid = 21 and NOT ((endTime < to_Date('2021-08-14 00:00:00','yyyy-MM-dd hh24:mi:ss')) OR (startTime > to_Date('2021-08-18 00:00:00','yyyy-MM-dd hh24:mi:ss')))

 

//方法三

select d.dt_,count(typeid)
from house r,(SELECT TO_DATE('2021-08-29 00:00:00', 'yyyy-MM-dd hh24:mi:ss') + (LEVEL-1) dt_ FROM dual CONNECT BY LEVEL <=31 ) d
where r.state = 0 and NOT ((r.checkouttime < d.dt_) OR (r.resvertime > d.dt_+1-1/(24*60*60)))
group by d.dt_ order by d.dt_

 

 

实际应用

-- 查找出时间上有交集的班级  A
select classroom_id
from s_theory_classroom
where 1 = 1 and NOT(end_time < '2022-09-01 15:00:00') and NOT(attend_time > '2022-09-01 20:00:00')

-- 查找出时间上有交集的班级  B
select *
from s_theory_classroom
where 1 = 1 and NOT ( (end_time < '2022-09-01 15:00:00') OR (attend_time > '2022-09-01 20:00:00') )

 

查找老师代课班级以及时间不冲突的班级

select * from t_class
where id in (select ttc.class_id from t_teacher_class ttc left join t_teacher tt on ttc.teacher_id = tt.id where tt.user_id = 1207)
　　　　AND
(id not in
　　　　(select classroom_id from s_theory_classroom
　　　　　　where 1 = 1 and NOT(end_time < '2022-09-01 15:00:00') and NOT(attend_time > '2022-09-01 20:00:00')
　　　　)
)