【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')
)
)