表A (信息录入表) 表B(出差表)
ID WRITE_DATE TEXT ID BEGIN_DATE END_DATE
1 2010.4.5 tttttt1 1 2010.4.7 2010.4.9
1 2010.4.6 tttttt2 1 2010.4.11 2010.4.12
1 2010.4.7 tttttt3 2 2010.4.8 2010.4.10
1 2010.4.8 tttttt4
1 2010.4.9 tttttt5
1 2010.4.10 tttttt6
1 2010.4.11 tttttt7
1 2010.4.12 tttttt8
1 2010.4.13 tttttt9
1 2010.4.14 tttttt10
2 2010.4.5 ssssss1
2 2010.4.6 ssssss2
2 2010.4.7 ssssss3
2 2010.4.8 ssssss4
2 2010.4.9 ssssss5
2 2010.4.10 ssssss6
2 2010.4.11 ssssss7ID是员工ID,WRITE_DATE是录入日期,TEXT是内容
BEGIN_DATE是出差起始日期,END_DATE是出差结束日期。我现在想要查询没有出差的时候所录入的信息结果。结果就是
1 2010.4.5 tttttt1
1 2010.4.6 tttttt2
1 2010.4.10 tttttt6
1 2010.4.13 tttttt9
1 2010.4.14 tttttt10
2 2010.4.5 ssssss1
2 2010.4.6 ssssss2
2 2010.4.7 ssssss3
2 2010.4.11 ssssss7
不用循环怎么写SELECT?
ID WRITE_DATE TEXT ID BEGIN_DATE END_DATE
1 2010.4.5 tttttt1 1 2010.4.7 2010.4.9
1 2010.4.6 tttttt2 1 2010.4.11 2010.4.12
1 2010.4.7 tttttt3 2 2010.4.8 2010.4.10
1 2010.4.8 tttttt4
1 2010.4.9 tttttt5
1 2010.4.10 tttttt6
1 2010.4.11 tttttt7
1 2010.4.12 tttttt8
1 2010.4.13 tttttt9
1 2010.4.14 tttttt10
2 2010.4.5 ssssss1
2 2010.4.6 ssssss2
2 2010.4.7 ssssss3
2 2010.4.8 ssssss4
2 2010.4.9 ssssss5
2 2010.4.10 ssssss6
2 2010.4.11 ssssss7ID是员工ID,WRITE_DATE是录入日期,TEXT是内容
BEGIN_DATE是出差起始日期,END_DATE是出差结束日期。我现在想要查询没有出差的时候所录入的信息结果。结果就是
1 2010.4.5 tttttt1
1 2010.4.6 tttttt2
1 2010.4.10 tttttt6
1 2010.4.13 tttttt9
1 2010.4.14 tttttt10
2 2010.4.5 ssssss1
2 2010.4.6 ssssss2
2 2010.4.7 ssssss3
2 2010.4.11 ssssss7
不用循环怎么写SELECT?
select a.*
from a,b
where a.id=b.id
and a.write_date not
between (
select b.begin_date
from b
where a.id=b.id)
and (
select b.end_date
from b
where a.id=b.id)
-- 用户明细表
WITH tmp_a AS
(
select 1 id, to_date('20100405','yyyymmdd') write_date, 'tttttt1' text from dual union all
select 1 id, to_date('20100406','yyyymmdd') write_date, 'tttttt2' text from dual union all
select 1 id, to_date('20100407','yyyymmdd') write_date, 'tttttt3' text from dual union all
select 1 id, to_date('20100408','yyyymmdd') write_date, 'tttttt4' text from dual union all
select 1 id, to_date('20100409','yyyymmdd') write_date, 'tttttt5' text from dual union all
select 1 id, to_date('20100410','yyyymmdd') write_date, 'tttttt6' text from dual union all
select 1 id, to_date('20100411','yyyymmdd') write_date, 'tttttt7' text from dual union all
select 1 id, to_date('20100412','yyyymmdd') write_date, 'tttttt8' text from dual union all
select 1 id, to_date('20100413','yyyymmdd') write_date, 'tttttt9' text from dual union all
select 1 id, to_date('20100414','yyyymmdd') write_date, 'tttttt10' text from dual union all
select 2 id, to_date('20100405','yyyymmdd') write_date, 'ssssss1' text from dual union all
select 2 id, to_date('20100406','yyyymmdd') write_date, 'ssssss2' text from dual union all
select 2 id, to_date('20100407','yyyymmdd') write_date, 'ssssss3' text from dual union all
select 2 id, to_date('20100408','yyyymmdd') write_date, 'ssssss4' text from dual union all
select 2 id, to_date('20100409','yyyymmdd') write_date, 'ssssss5' text from dual union all
select 2 id, to_date('20100410','yyyymmdd') write_date, 'ssssss6' text from dual union all
select 2 id, to_date('20100411','yyyymmdd') write_date, 'ssssss7' text from dual
)
-- 出差记录表
, tmp_b AS
(
select 1 id, to_date('20100407','yyyymmdd') BEGIN_DATE, to_date('20100409','yyyymmdd') END_DATE from dual union all
select 1 id, to_date('20100411','yyyymmdd') BEGIN_DATE, to_date('20100412','yyyymmdd') END_DATE from dual union all
select 2 id, to_date('20100408','yyyymmdd') BEGIN_DATE, to_date('20100410','yyyymmdd') END_DATE 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 a.id,a.write_date --,a.text
--,b.id,b.BEGIN_DATE,b.END_DATE
FROM tmp_a a
LEFT JOIN tmp_b b ON a.id = b.id
WHERE a.write_date BETWEEN b.BEGIN_DATE AND b.END_DATE
-- ORDER BY a.id,a.write_date
)
-------------------------------- 过滤 not between and (返回多条,不唯一) -----------------------------
/*
问题:
表A (信息录入表) 表B(出差表)
ID WRITE_DATE TEXT ID BEGIN_DATE END_DATE
1 2010.4.5 tttttt1 1 2010.4.7 2010.4.9
1 2010.4.6 tttttt2 1 2010.4.11 2010.4.12
1 2010.4.7 tttttt3 2 2010.4.8 2010.4.10
1 2010.4.8 tttttt4
1 2010.4.9 tttttt5
1 2010.4.10 tttttt6
1 2010.4.11 tttttt7
1 2010.4.12 tttttt8
1 2010.4.13 tttttt9
1 2010.4.14 tttttt10
2 2010.4.5 ssssss1
2 2010.4.6 ssssss2
2 2010.4.7 ssssss3
2 2010.4.8 ssssss4
2 2010.4.9 ssssss5
2 2010.4.10 ssssss6
2 2010.4.11 ssssss7 ID是员工ID,WRITE_DATE是录入日期,TEXT是内容
BEGIN_DATE是出差起始日期,END_DATE是出差结束日期。 我现在想要查询没有出差的时候所录入的信息结果。 结果就是
1 2010.4.5 tttttt1
1 2010.4.6 tttttt2
1 2010.4.10 tttttt6
1 2010.4.13 tttttt9
1 2010.4.14 tttttt10
2 2010.4.5 ssssss1
2 2010.4.6 ssssss2
2 2010.4.7 ssssss3
2 2010.4.11 ssssss7
不用循环怎么写SELECT?
*/
-- 测试数据 -- 用户明细表
WITH tmp_a AS
(
select 1 id, to_date('20100405','yyyymmdd') write_date, 'tttttt1' text from dual union all
select 1 id, to_date('20100406','yyyymmdd') write_date, 'tttttt2' text from dual union all
select 1 id, to_date('20100407','yyyymmdd') write_date, 'tttttt3' text from dual union all
select 1 id, to_date('20100408','yyyymmdd') write_date, 'tttttt4' text from dual union all
select 1 id, to_date('20100409','yyyymmdd') write_date, 'tttttt5' text from dual union all
select 1 id, to_date('20100410','yyyymmdd') write_date, 'tttttt6' text from dual union all
select 1 id, to_date('20100411','yyyymmdd') write_date, 'tttttt7' text from dual union all
select 1 id, to_date('20100412','yyyymmdd') write_date, 'tttttt8' text from dual union all
select 1 id, to_date('20100413','yyyymmdd') write_date, 'tttttt9' text from dual union all
select 1 id, to_date('20100414','yyyymmdd') write_date, 'tttttt10' text from dual union all
select 2 id, to_date('20100405','yyyymmdd') write_date, 'ssssss1' text from dual union all
select 2 id, to_date('20100406','yyyymmdd') write_date, 'ssssss2' text from dual union all
select 2 id, to_date('20100407','yyyymmdd') write_date, 'ssssss3' text from dual union all
select 2 id, to_date('20100408','yyyymmdd') write_date, 'ssssss4' text from dual union all
select 2 id, to_date('20100409','yyyymmdd') write_date, 'ssssss5' text from dual union all
select 2 id, to_date('20100410','yyyymmdd') write_date, 'ssssss6' text from dual union all
select 2 id, to_date('20100411','yyyymmdd') write_date, 'ssssss7' text from dual
)
-- 出差记录表
, tmp_b AS
(
select 1 id, to_date('20100407','yyyymmdd') BEGIN_DATE, to_date('20100409','yyyymmdd') END_DATE from dual union all
select 1 id, to_date('20100411','yyyymmdd') BEGIN_DATE, to_date('20100412','yyyymmdd') END_DATE from dual union all
select 2 id, to_date('20100408','yyyymmdd') BEGIN_DATE, to_date('20100410','yyyymmdd') END_DATE 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 --,a.text
--,b.id,b.BEGIN_DATE,b.END_DATE
FROM tmp_a a
INNER JOIN tmp_b b ON a.id = b.id
WHERE a.write_date BETWEEN b.BEGIN_DATE AND b.END_DATE
-- ORDER BY a.id,a.write_date
)
那么直接
select * from table_name t where t.BEGIN_DATE is null;
2 (
3 select 1 id, to_date('20100405','yyyymmdd') write_date, 'tttttt1' text from dual union all
4 select 1 id, to_date('20100406','yyyymmdd') write_date, 'tttttt2' text from dual union all
5 select 1 id, to_date('20100407','yyyymmdd') write_date, 'tttttt3' text from dual union all
6 select 1 id, to_date('20100408','yyyymmdd') write_date, 'tttttt4' text from dual union all
7 select 1 id, to_date('20100409','yyyymmdd') write_date, 'tttttt5' text from dual union all
8 select 1 id, to_date('20100410','yyyymmdd') write_date, 'tttttt6' text from dual union all
9 select 1 id, to_date('20100411','yyyymmdd') write_date, 'tttttt7' text from dual union all
10 select 1 id, to_date('20100412','yyyymmdd') write_date, 'tttttt8' text from dual union all
11 select 1 id, to_date('20100413','yyyymmdd') write_date, 'tttttt9' text from dual union all
12 select 1 id, to_date('20100414','yyyymmdd') write_date, 'tttttt10' text from dual union all
13 select 2 id, to_date('20100405','yyyymmdd') write_date, 'ssssss1' text from dual union all
14 select 2 id, to_date('20100406','yyyymmdd') write_date, 'ssssss2' text from dual union all
15 select 2 id, to_date('20100407','yyyymmdd') write_date, 'ssssss3' text from dual union all
16 select 2 id, to_date('20100408','yyyymmdd') write_date, 'ssssss4' text from dual union all
17 select 2 id, to_date('20100409','yyyymmdd') write_date, 'ssssss5' text from dual union all
18 select 2 id, to_date('20100410','yyyymmdd') write_date, 'ssssss6' text from dual union all
19 select 2 id, to_date('20100411','yyyymmdd') write_date, 'ssssss7' text from dual
20 )
21 , tmp_b AS
22 (
23 select 1 id, to_date('20100407','yyyymmdd') BEGIN_DATE, to_date('20100409','yyyymmdd') END_DATE from dual union all
24 select 1 id, to_date('20100411','yyyymmdd') BEGIN_DATE, to_date('20100412','yyyymmdd') END_DATE from dual union all
25 select 2 id, to_date('20100408','yyyymmdd') BEGIN_DATE, to_date('20100410','yyyymmdd') END_DATE from dual
26 )
27 SELECT s.id,s.write_date,s.text
28 FROM tmp_a s
29 WHERE (s.id,s.write_date)
30 NOT IN
31 (SELECT DISTINCT a.id,a.write_date --,a.text
32 --,b.id,b.BEGIN_DATE,b.END_DATE
33 FROM tmp_a a
34 INNER JOIN tmp_b b ON a.id = b.id
35 WHERE a.write_date BETWEEN b.BEGIN_DATE AND b.END_DATE
36 -- ORDER BY a.id,a.write_date
37 )
38 / 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 ssssss79 rows selected