常常碰到要校验数据范围(时间上)是否存在重叠冲突的情况,典型的场景有检测住院人员出入院时间、酒店房间预订等;
这种问题是在一个表中,针对具体的同一个事物来判断(检测住院人员出入院时间针对同一个住院人员,酒店房间预订则针对同一间房);
以房间预订为例:
假如房间888号已经有9月1日-9月10日的预订记录,当其它客人再来预订时,系统必须判断,不能与这个日期范围产生重叠。
有四种情况会产生重叠:
1、全包含:例如用户企图预订8月30日-9月15日;
2、被包含:例如用户企图预订9月5日-9月6日;
3、下限包含:例如用户企图预订8月30日-9月2日;
4、上限包含:例如用户企图预订9月8日-9月15日。
其它三种情况:
1、前期顺预订:例如用户企图预订8月30日-9月1日;
2. 后期顺预订:例如用户企图预订9月10日-9月15日;
3. 日期一致: 例如用户企图预订9月1日-9月10日;
-- 搭建测试表及数据
create table T_TIME_OVERLAP (
ID NUMBER(16) not null,
psn_id NUMBER(16),
inbed_dt DATE,
outbed_dt DATE,
bz char(1)
) ;
alter table T_TIME_OVERLAP add constraint PK_T_TIME_OVERLAP primary key (ID) ;
comment on table T_TIME_OVERLAP is '测试时间重叠表';
comment on column T_TIME_OVERLAP.ID is '表记录序号';
comment on column T_TIME_OVERLAP.psn_id is '个人身份证号';
comment on column T_TIME_OVERLAP.inbed_dt is '入日期';
comment on column T_TIME_OVERLAP.outbed_dt is '出日期';
comment on column T_TIME_OVERLAP.bz is '备注';
INSERT INTO T_TIME_OVERLAP VALUES (1,110123,to_date('2016-09-01','yyyy-mm-dd'),to_date('2016-09-10','yyyy-mm-dd'),'0'); -- 初始数据
INSERT INTO T_TIME_OVERLAP VALUES (2,110123,to_date('2016-08-30','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'1'); -- 测试全包
INSERT INTO T_TIME_OVERLAP VALUES (3,110123,to_date('2016-09-05','yyyy-mm-dd'),to_date('2016-09-06','yyyy-mm-dd'),'1'); -- 被包含
INSERT INTO T_TIME_OVERLAP VALUES (4,110123,to_date('2016-08-30','yyyy-mm-dd'),to_date('2016-09-02','yyyy-mm-dd'),'1');
INSERT INTO T_TIME_OVERLAP VALUES (5,110123,to_date('2016-09-08','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'1');
INSERT INTO T_TIME_OVERLAP VALUES (6,110123,to_date('2016-08-30','yyyy-mm-dd'),to_date('2016-09-01','yyyy-mm-dd'),'2');
INSERT INTO T_TIME_OVERLAP VALUES (7,110123,to_date('2016-09-10','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'2');
INSERT INTO T_TIME_OVERLAP VALUES (8,110123,to_date('2016-09-10','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'3');
INSERT INTO T_TIME_OVERLAP VALUES (10,220123,to_date('2016-09-10','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'4'); -- 干扰数据1
INSERT INTO T_TIME_OVERLAP VALUES (11,220123,to_date('2016-09-01','yyyy-mm-dd'),to_date('2016-09-10','yyyy-mm-dd'),'4');
INSERT INTO T_TIME_OVERLAP VALUES (13,330123,to_date('2016-09-10','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'1');
commit ;
---------
正确的写法如下:
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and (a.inbed_dt between b.inbed_dt and b.outbed_dt or
a.outbed_dt between b.inbed_dt and b.outbed_dt or
b.inbed_dt between a.inbed_dt and a.outbed_dt or
b.outbed_dt between a.inbed_dt and a.outbed_dt)
order by a.id, b.id; -- 14 行
推倒出一个精简公式:
a_start <= b_end and a_end >= b_start
-- 查询示例
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and (a.inbed_dt between b.inbed_dt and b.outbed_dt
or a.outbed_dt between b.inbed_dt and b.outbed_dt
or b.inbed_dt between a.inbed_dt and a.outbed_dt
or b.outbed_dt between a.inbed_dt and a.outbed_dt
)
order by a.id, b.id ; -- 14 行
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and a.bz in ('0','1')
and b.bz in ('0','1')
and a.inbed_dt <= b.outbed_dt
and a.outbed_dt >= b.inbed_dt
order by a.id, b.id ; --14 行
--------------------------------------------------
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and a.bz in ('0','1','2')
and b.bz in ('0','1','2')
and (a.inbed_dt between b.inbed_dt and b.outbed_dt
or a.outbed_dt between b.inbed_dt and b.outbed_dt
or b.inbed_dt between a.inbed_dt and a.outbed_dt
or b.outbed_dt between a.inbed_dt and a.outbed_dt
)
order by a.id, b.id ; --26行
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and a.bz in ('0','1','2')
and b.bz in ('0','1','2')
and a.inbed_dt <= b.outbed_dt
and a.outbed_dt >= b.inbed_dt
order by a.id, b.id ; -- 26行
--------------------------------------------------
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and a.bz in ('0','1','2','3')
and b.bz in ('0','1','2','3')
and (a.inbed_dt between b.inbed_dt and b.outbed_dt
or a.outbed_dt between b.inbed_dt and b.outbed_dt
or b.inbed_dt between a.inbed_dt and a.outbed_dt
or b.outbed_dt between a.inbed_dt and a.outbed_dt
)
order by a.id, b.id ; --34 行
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and a.bz in ('0','1','2','3')
and b.bz in ('0','1','2','3')
and a.inbed_dt <= b.outbed_dt
and a.outbed_dt >= b.inbed_dt
order by a.id, b.id ; --34行
--------------------------------------------------
这种问题是在一个表中,针对具体的同一个事物来判断(检测住院人员出入院时间针对同一个住院人员,酒店房间预订则针对同一间房);
以房间预订为例:
假如房间888号已经有9月1日-9月10日的预订记录,当其它客人再来预订时,系统必须判断,不能与这个日期范围产生重叠。
有四种情况会产生重叠:
1、全包含:例如用户企图预订8月30日-9月15日;
2、被包含:例如用户企图预订9月5日-9月6日;
3、下限包含:例如用户企图预订8月30日-9月2日;
4、上限包含:例如用户企图预订9月8日-9月15日。
其它三种情况:
1、前期顺预订:例如用户企图预订8月30日-9月1日;
2. 后期顺预订:例如用户企图预订9月10日-9月15日;
3. 日期一致: 例如用户企图预订9月1日-9月10日;
-- 搭建测试表及数据
create table T_TIME_OVERLAP (
ID NUMBER(16) not null,
psn_id NUMBER(16),
inbed_dt DATE,
outbed_dt DATE,
bz char(1)
) ;
alter table T_TIME_OVERLAP add constraint PK_T_TIME_OVERLAP primary key (ID) ;
comment on table T_TIME_OVERLAP is '测试时间重叠表';
comment on column T_TIME_OVERLAP.ID is '表记录序号';
comment on column T_TIME_OVERLAP.psn_id is '个人身份证号';
comment on column T_TIME_OVERLAP.inbed_dt is '入日期';
comment on column T_TIME_OVERLAP.outbed_dt is '出日期';
comment on column T_TIME_OVERLAP.bz is '备注';
INSERT INTO T_TIME_OVERLAP VALUES (1,110123,to_date('2016-09-01','yyyy-mm-dd'),to_date('2016-09-10','yyyy-mm-dd'),'0'); -- 初始数据
INSERT INTO T_TIME_OVERLAP VALUES (2,110123,to_date('2016-08-30','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'1'); -- 测试全包
INSERT INTO T_TIME_OVERLAP VALUES (3,110123,to_date('2016-09-05','yyyy-mm-dd'),to_date('2016-09-06','yyyy-mm-dd'),'1'); -- 被包含
INSERT INTO T_TIME_OVERLAP VALUES (4,110123,to_date('2016-08-30','yyyy-mm-dd'),to_date('2016-09-02','yyyy-mm-dd'),'1');
INSERT INTO T_TIME_OVERLAP VALUES (5,110123,to_date('2016-09-08','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'1');
INSERT INTO T_TIME_OVERLAP VALUES (6,110123,to_date('2016-08-30','yyyy-mm-dd'),to_date('2016-09-01','yyyy-mm-dd'),'2');
INSERT INTO T_TIME_OVERLAP VALUES (7,110123,to_date('2016-09-10','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'2');
INSERT INTO T_TIME_OVERLAP VALUES (8,110123,to_date('2016-09-10','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'3');
INSERT INTO T_TIME_OVERLAP VALUES (10,220123,to_date('2016-09-10','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'4'); -- 干扰数据1
INSERT INTO T_TIME_OVERLAP VALUES (11,220123,to_date('2016-09-01','yyyy-mm-dd'),to_date('2016-09-10','yyyy-mm-dd'),'4');
INSERT INTO T_TIME_OVERLAP VALUES (13,330123,to_date('2016-09-10','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'1');
commit ;
---------
正确的写法如下:
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and (a.inbed_dt between b.inbed_dt and b.outbed_dt or
a.outbed_dt between b.inbed_dt and b.outbed_dt or
b.inbed_dt between a.inbed_dt and a.outbed_dt or
b.outbed_dt between a.inbed_dt and a.outbed_dt)
order by a.id, b.id; -- 14 行
推倒出一个精简公式:
a_start <= b_end and a_end >= b_start
-- 查询示例
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and (a.inbed_dt between b.inbed_dt and b.outbed_dt
or a.outbed_dt between b.inbed_dt and b.outbed_dt
or b.inbed_dt between a.inbed_dt and a.outbed_dt
or b.outbed_dt between a.inbed_dt and a.outbed_dt
)
order by a.id, b.id ; -- 14 行
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and a.bz in ('0','1')
and b.bz in ('0','1')
and a.inbed_dt <= b.outbed_dt
and a.outbed_dt >= b.inbed_dt
order by a.id, b.id ; --14 行
--------------------------------------------------
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and a.bz in ('0','1','2')
and b.bz in ('0','1','2')
and (a.inbed_dt between b.inbed_dt and b.outbed_dt
or a.outbed_dt between b.inbed_dt and b.outbed_dt
or b.inbed_dt between a.inbed_dt and a.outbed_dt
or b.outbed_dt between a.inbed_dt and a.outbed_dt
)
order by a.id, b.id ; --26行
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and a.bz in ('0','1','2')
and b.bz in ('0','1','2')
and a.inbed_dt <= b.outbed_dt
and a.outbed_dt >= b.inbed_dt
order by a.id, b.id ; -- 26行
--------------------------------------------------
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and a.bz in ('0','1','2','3')
and b.bz in ('0','1','2','3')
and (a.inbed_dt between b.inbed_dt and b.outbed_dt
or a.outbed_dt between b.inbed_dt and b.outbed_dt
or b.inbed_dt between a.inbed_dt and a.outbed_dt
or b.outbed_dt between a.inbed_dt and a.outbed_dt
)
order by a.id, b.id ; --34 行
select a.id, b.id
from T_TIME_OVERLAP a, T_TIME_OVERLAP b
where a.psn_id = b.psn_id
and a.id <> b.id
and a.bz in ('0','1','2','3')
and b.bz in ('0','1','2','3')
and a.inbed_dt <= b.outbed_dt
and a.outbed_dt >= b.inbed_dt
order by a.id, b.id ; --34行
--------------------------------------------------