现有个工作记录:
A 2008-08-28 15:00 到 2008-08-28 15:59 有安排现在要添加新的工作记录 B 2008-08-28 08:00到 2008-08-29 07:59,
首先就要查询出A记录,并过滤.
但是发现下面SQL过滤不了,但是有些时段却能过滤。
select * FROM test
WHERE gzid='00001' and ((to_date('2008-08-28 08:00','yyyy-MM-dd hh24:mi:ss') >= kssj and to_date('2008-08-28 08:00','yyyy-MM-dd hh24:mi:ss') < jssj
or (to_date('2008-08-29 07:59','yyyy-MM-dd hh24:mi:ss') > kssj and to_date('2008-08-29 07:59','yyyy-MM-dd hh24:mi:ss') <= jssj))
A 2008-08-28 15:00 到 2008-08-28 15:59 有安排现在要添加新的工作记录 B 2008-08-28 08:00到 2008-08-29 07:59,
首先就要查询出A记录,并过滤.
但是发现下面SQL过滤不了,但是有些时段却能过滤。
select * FROM test
WHERE gzid='00001' and ((to_date('2008-08-28 08:00','yyyy-MM-dd hh24:mi:ss') >= kssj and to_date('2008-08-28 08:00','yyyy-MM-dd hh24:mi:ss') < jssj
or (to_date('2008-08-29 07:59','yyyy-MM-dd hh24:mi:ss') > kssj and to_date('2008-08-29 07:59','yyyy-MM-dd hh24:mi:ss') <= jssj))
and kssj <to_date('2008-08-29 07:59','yyyy-MM-dd hh24:mi:ss') and kssj>=to_date('2008-08-28 08:00','yyyy-MM-dd hh24:mi:ss')
and jssj <to_date('2008-08-29 07:59','yyyy-MM-dd hh24:mi:ss') and jssj>=to_date('2008-08-28 08:00','yyyy-MM-dd hh24:mi:ss')
--------------------------------------
SQL是这样的意思嘛?
drop table a;
create table a(gzid varchar2(10), kssj date, jssj date);
insert into a
values
('00001',
to_date('2008-08-28 15:00', 'yyyy-mm-dd hh24:mi'),
to_date('2008-08-28 15:59','yyyy-mm-dd hh24:mi'));
commit;
select *
FROM a
WHERE gzid = '00001'
and (kssj <= to_date('2008-08-28 08:00', 'yyyy-MM-dd hh24:mi:ss') and
jssj > to_date('2008-08-28 08:00', 'yyyy-MM-dd hh24:mi:ss') or
(kssj < to_date('2008-08-29 07:59', 'yyyy-MM-dd hh24:mi:ss') and
jssj >= to_date('2008-08-29 07:59', 'yyyy-MM-dd hh24:mi:ss')));
结果三个字段都没有值啊.这不过滤掉了嘛...
values
('00001',
to_date('2008-08-28 15:00', 'yyyy-mm-dd hh24:mi'),
to_date('2008-08-28 15:59','yyyy-mm-dd hh24:mi'));
insert into a
values
('00001',
to_date('2008-08-27 15:00', 'yyyy-mm-dd hh24:mi'),
to_date('2008-08-28 15:59','yyyy-mm-dd hh24:mi'));
insert into a
values
('00001',
to_date('2008-08-28 15:00', 'yyyy-mm-dd hh24:mi'),
to_date('2008-08-29 15:59','yyyy-mm-dd hh24:mi'));
insert into a
values
('00001',
to_date('2008-08-27 15:00', 'yyyy-mm-dd hh24:mi'),
to_date('2008-08-29 15:59','yyyy-mm-dd hh24:mi'));
insert into a
values
('00001',
to_date('2008-08-25 15:00', 'yyyy-mm-dd hh24:mi'),
to_date('2008-08-25 15:59','yyyy-mm-dd hh24:mi'));
commit;
--找出有冲突的记录 :
SELECT *
FROM a
WHERE gzid = '00001'
and jssj >= TO_DATE ('2008-08-28 08:00', 'yyyy-MM-dd hh24:mi:ss')
and kssj <= TO_DATE ('2008-08-29 07:59', 'yyyy-MM-dd hh24:mi:ss')
--结果:
GZID|KSSJ|JSSJ
00001|2008-8-28 15:00:00|2008-8-28 15:59:00
00001|2008-8-27 15:00:00|2008-8-28 15:59:00
00001|2008-8-28 15:00:00|2008-8-29 15:59:00
00001|2008-8-27 15:00:00|2008-8-29 15:59:00
--找出没冲突的记录:
SELECT *
FROM a
WHERE gzid = '00001'
AND ( jssj < TO_DATE ('2008-08-28 08:00', 'yyyy-MM-dd hh24:mi:ss')
OR kssj > TO_DATE ('2008-08-29 07:59', 'yyyy-MM-dd hh24:mi:ss')
)--结果:
GZID|KSSJ|JSSJ
00001|2008-8-25 15:00:00|2008-8-25 15:59:00
SELECT *
FROM a
WHERE gzid = '00001'
and jssj >= TO_DATE ('2008-08-28 08:00', 'yyyy-MM-dd hh24:mi:ss')
and kssj <= TO_DATE ('2008-08-29 07:59', 'yyyy-MM-dd hh24:mi:ss')