ORACLE的SQL练习---3. 分析时间交叉重叠的记录

由于系统问题造成某天会议室预定时间冲突，预定的开会时间如下：

需要查询出开会时间有重叠的是哪几条预定记录。

期望结果：

建表语句：

create table LX_04_N
(
  id        CHAR(1),
  starttime DATE,
  endtime   DATE
);

插入语句

insert into lx_04_n (ID, STARTTIME, ENDTIME)
values ('1', to_date('15-01-2020 08:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-01-2020 09:15:00', 'dd-mm-yyyy hh24:mi:ss'));
commit;
insert into lx_04_n (ID, STARTTIME, ENDTIME)
values ('3', to_date('15-01-2020 10:20:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-01-2020 12:20:00', 'dd-mm-yyyy hh24:mi:ss'));
commit;
insert into lx_04_n (ID, STARTTIME, ENDTIME)
values ('2', to_date('15-01-2020 10:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-01-2020 14:00:00', 'dd-mm-yyyy hh24:mi:ss'));
commit;
insert into lx_04_n (ID, STARTTIME, ENDTIME)
values ('4', to_date('15-01-2020 14:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-01-2020 15:00:00', 'dd-mm-yyyy hh24:mi:ss'));
commit;
insert into lx_04_n (ID, STARTTIME, ENDTIME)
values ('5', to_date('15-01-2020 15:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-01-2020 16:30:00', 'dd-mm-yyyy hh24:mi:ss'));
commit;
insert into lx_04_n (ID, STARTTIME, ENDTIME)
values ('6', to_date('15-01-2020 16:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-01-2020 17:30:00', 'dd-mm-yyyy hh24:mi:ss'));
commit;
insert into lx_04_n (ID, STARTTIME, ENDTIME)
values ('7', to_date('15-01-2020 18:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-01-2020 19:00:00', 'dd-mm-yyyy hh24:mi:ss'));
commit;
insert into lx_04_n (ID, STARTTIME, ENDTIME)
values ('8', to_date('15-01-2020 19:45:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-01-2020 21:45:00', 'dd-mm-yyyy hh24:mi:ss'));
commit;
insert into lx_04_n (ID, STARTTIME, ENDTIME)
values ('9', to_date('15-01-2020 19:45:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-01-2020 21:45:00', 'dd-mm-yyyy hh24:mi:ss'));
commit;

答案：

select a.*,b.*  from lx_04_n a, lx_04_n b where a.id <> b.id and a.starttime <= b.starttime and a.endtime > b.starttime
union 
select a.*,b.*   from lx_04_n a, lx_04_n b where a.id <> b.id and a.starttime < b.endtime and a.endtime >= b.endtime
union 
select a.*,b.*   from lx_04_n a, lx_04_n b where a.id <> b.id and a.starttime >= b.starttime and a.endtime <= b.endtime
union 
select a.*,b.*   from lx_04_n a, lx_04_n b where a.id <> b.id and a.starttime < b.starttime and a.endtime > b.endtime

解题思路：

如上图，最上面的线段相当于b表，(a.b,c,d)四个线段为a表。两个时间段发生重合只有a,b,c,d四种可能。
第一种情况：a线段左右两端（起始时间和终止时间）与b表的最左端（起始时间）来比较。
第二种情况：b线段左右两端（起始时间和终止时间）与b表的最右端（终止时间）来比较。
第三、四种情况：c出现段的最左端（起始时间）与b表的最左端（起始时间）比较，c出现段的最右端（终止时间）与b表的最右端（终止时间）比较。