select a01 from type_file where a02='N' minus select a01 from type_file where a02='Y'
SQL> with tmp as 2 ( 3 select '01' a01, 'Y' a02 from dual 4 union all 5 select '01' a01, 'N' a02 from dual 6 union all 7 select '02' a01, 'N' a02 from dual 8 union all 9 select '02' a01, 'N' a02 from dual 10 union all 11 select '02' a01, 'N' a02 from dual 12 union all 13 select '03' a01, 'N' a02 from dual 14 ) 15 select a01 from tmp where a02='N' 16 minus 17 select a01 from tmp where a02='Y';
A01 --- 02 03 这个是写死了。 假设是还其他字母代替,或者Y和N互换呢,情况又是乍样?
select *from tablename where a01 not in (select a01 from tablename where a02='y') 不知道楼主是不是想要这样的结果,希望能和楼主一起探讨下oracle,qq75360175
select distinct a01 from type_file where a01 not in (select a01 from type_file where a02='Y')
SQL> select distinct a01 2 from type_file 3 where a01 not in (select a01 4 from (select a01, a02, count(*) as total 5 from type_file 6 group by a01, a02) 7 where a02 = 'Y' 8 and total = 1);A01 ---------- 02 03 这个应该满足你的要求吧。
我想做的查询:select distinct a01 from type_file where ... 要求就是如果a01对应的a02中只要有一笔为Y的,查询结果中就不显示这笔a01。 select distinct a01 from type_file where a01 not in (select a01 from type_file where a02='Y')
minus
select a01 from type_file where a02='Y'
2 (
3 select '01' a01, 'Y' a02 from dual
4 union all
5 select '01' a01, 'N' a02 from dual
6 union all
7 select '02' a01, 'N' a02 from dual
8 union all
9 select '02' a01, 'N' a02 from dual
10 union all
11 select '02' a01, 'N' a02 from dual
12 union all
13 select '03' a01, 'N' a02 from dual
14 )
15 select a01 from tmp where a02='N'
16 minus
17 select a01 from tmp where a02='Y';
A01
---
02
03
这个是写死了。 假设是还其他字母代替,或者Y和N互换呢,情况又是乍样?
a02='y')
不知道楼主是不是想要这样的结果,希望能和楼主一起探讨下oracle,qq75360175
2 from type_file
3 where a01 not in (select a01
4 from (select a01, a02, count(*) as total
5 from type_file
6 group by a01, a02)
7 where a02 = 'Y'
8 and total = 1);A01
----------
02
03
这个应该满足你的要求吧。
要求就是如果a01对应的a02中只要有一笔为Y的,查询结果中就不显示这笔a01。
select distinct a01 from type_file where a01 not in (select a01 from type_file where a02='Y')