oracle 不要乱用between and

4yjube801m

　　//需求：//查询出用户在没有出差的时候录入的信息//分析：//子查询：我们可以显查询出那些在出差时录入信息的用户id，和时间//主查询：我们从用户明细表中检索信息，条件是，用户id和write_date不在子查询查到的id和时间////用户明细表with tmp_a as(select 1 id,to_date('20100405','yyyymmdd') write_date,'tttttt1'  text from dual union allselect 1,to_date('20100406','yyyymmdd'),'tttttt2' from dual union allselect 1,to_date('20100407','yyyymmdd'),'tttttt3' from dual union allselect 1,to_date('20100408','yyyymmdd'),'tttttt4' from dual union allselect 1,to_date('20100409','yyyymmdd'),'tttttt5' from dual union allselect 1,to_date('20100410','yyyymmdd'),'tttttt6' from dual union allselect 1,to_date('20100411','yyyymmdd'),'tttttt7' from dual union allselect 1,to_date('20100412','yyyymmdd'),'tttttt8' from dual union allselect 1,to_date('20100413','yyyymmdd'),'tttttt9' from dual union allselect 1,to_date('20100414','yyyymmdd'),'tttttt10' from dual union allselect 2,to_date('20100405','yyyymmdd'),'ssssss1' from dual union allselect 2,to_date('20100406','yyyymmdd'),'ssssss2' from dual union allselect 2,to_date('20100407','yyyymmdd'),'ssssss3' from dual union allselect 2,to_date('20100408','yyyymmdd'),'ssssss4' from dual union allselect 2,to_date('20100409','yyyymmdd'),'ssssss5' from dual union allselect 2,to_date('20100410','yyyymmdd'),'ssssss6' from dual union allselect 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 allselect 1,to_date('20100411','yyyymmdd'),to_date('20100412','yyyymmdd') from dual union allselect 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.textfrom tmp_a swhere (s.id,s.write_date) not in(//存在出差记录的用户明细select distinct a.id,a.write_datefrom 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  tttttt11 2010-04-06  tttttt21 2010-04-10  tttttt61 2010-04-13  tttttt91 2010-04-14  tttttt102 2010-04-05  ssssss12 2010-04-06  ssssss22 2010-04-07  ssssss32 2010-04-11  ssssss7
　　原帖:http://topic.csdn.net/u/20110510/10/a9a411b2-1020-454b-b806-98260df12cf3.html?seed=1860652243&r=73232676#r_73232676