oracle 不能用and,Oracle不要乱用betweenand

//需求： //查询出用户在没有出差的时候录入的信息 //分析： //子查询：我们可以显查询出那些在出差时录入信息的用户id，和

//需求：

//查询出用户在没有出差的时候录入的信息

//分析：

//子查询：我们可以显查询出那些在出差时录入信息的用户id，，和时间

//主查询：我们从用户明细表中检索信息，条件是，用户id和write_date不在子查询查到的id和时间

//

//用户明细表

with tmp_a as(

select 1 id,to_date('20100405','yyyymmdd') write_date,'tttttt1' text from dual union all

select 1,to_date('20100406','yyyymmdd'),'tttttt2' from dual union all

select 1,to_date('20100407','yyyymmdd'),'tttttt3' from dual union all

select 1,to_date('20100408','yyyymmdd'),'tttttt4' from dual union all

select 1,to_date('20100409','yyyymmdd'),'tttttt5' from dual union all

select 1,to_date('20100410','yyyymmdd'),'tttttt6' from dual union all

select 1,to_date('20100411','yyyymmdd'),'tttttt7' from dual union all

select 1,to_date('20100412','yyyymmdd'),'tttttt8' from dual union all

select 1,to_date('20100413','yyyymmdd'),'tttttt9' from dual union all

select 1,to_date('20100414','yyyymmdd'),'tttttt10' from dual union all

select 2,to_date('20100405','yyyymmdd'),'ssssss1' from dual union all

select 2,to_date('20100406','yyyymmdd'),'ssssss2' from dual union all

select 2,to_date('20100407','yyyymmdd'),'ssssss3' from dual union all

select 2,to_date('20100408','yyyymmdd'),'ssssss4' from dual union all

select 2,to_date('20100409','yyyymmdd'),'ssssss5' from dual union all

select 2,to_date('20100410','yyyymmdd'),'ssssss6' from dual union all

select 2,to_date('20100411','yyyymmdd'),'ssssss7' from dual)

//出差记录表

,tmp_b as(

select 1 id,to_date('20100407','yyyymmdd') begin_dt,to_date('20100409','yyyymmdd') end_dt from dual union all

select 1,to_date('20100411','yyyymmdd'),to_date('20100412','yyyymmdd') from dual union all

select 2,to_date('20100408','yyyymmdd'),to_date('20100410','yyyymmdd') from dual)

//查询 SQL

//需要解决的问题

//1. 同一用户，间隔出差(存在不同的出差启始时间) 不可简单使用 not between

//2. 有些用户，可能没有出差记录 不可简单使用 a.id = b.id

//通过过滤 (NOT IN) 查询出不存在出差记录的用户明细

select s.id,s.write_date,s.text

from tmp_a s

where (s.id,s.write_date) not in

(//存在出差记录的用户明细

select distinct a.id,a.write_date

from tmp_a a inner join tmp_b b

on a.id = b.id

where a.write_date

between b.begin_dt and b.end_dt

)

ID WRITE_DATE TEXT

---------- ----------- --------

1 2010-04-05 tttttt1

1 2010-04-06 tttttt2

1 2010-04-10 tttttt6

1 2010-04-13 tttttt9

1 2010-04-14 tttttt10

2 2010-04-05 ssssss1

2 2010-04-06 ssssss2

2 2010-04-07 ssssss3

2 2010-04-11 ssssss7

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：php中文网