select count(case when xb=1 and nl=20 then null else 1 end) from tb
只排除 同时满足的 select count(case when xb=1 and nl=20 then null else 1 end) from tb这句就可以了 我再问下 同样的条件 我要查 所有的记录 怎么查 ?
WITH t AS ( SELECT 1 XH,1 XB, 18 NL FROM dual UNION ALL SELECT 2,2, 20 FROM dual UNION ALL SELECT 3,1, 20 FROM dual UNION ALL SELECT 4,2, 18 FROM dual ) SELECT * FROM t WHERE xh NOT IN ( SELECT xh FROM t WHERE xb=1 AND nl=20 )--result: 1 1 18 2 2 20 4 2 18
WITH t AS ( SELECT 1 XH,1 XB, 18 NL FROM dual UNION ALL SELECT 2,2, 20 FROM dual UNION ALL SELECT 3,1, 20 FROM dual UNION ALL SELECT 4,2, 18 FROM dual ) SELECT COUNT(1) FROM t WHERE xh NOT IN ( SELECT xh FROM t WHERE xb=1 AND nl=20 )--RESULT: 3
select t.* from tb t where not exists(select 1 from tb where xb=t.xb and nl=t.nl and xb=1 and nl=20)
或者:select * from tb where (xb,nl) not in (select 1 as xb,20 as nl from dual)
select * from A where xb<>1 and NL<>20orselect * from A where xb not in(1) and NL not in(20)
where xb<>1 and NL<>20
你这个语句 条件 xb<>1 和 条件 NL<>20 是并集,只要满足其中一个 就没计算了
我需要的是同时满足这2个条件的条目 我要的是这2个条件的交集
select count(case when xb=1 or nl=20 then null else 1 end) from tb
你的结果只有1条记录但是很明显
1 1A 1 18
2 2A 2 20
4 3A 2 18这3条是不同时满足2个条件的记录
是的 我只需要排除 同时 xb=1 AND nl=20
的条目
select count(case when xb=1 and nl=20 then null
when xb<>1 and nl<>20 then null
else 1 end) from tb
2 2A 2 20
3 3A 1 20
4 3A 2 18
结果显示是:
4 3A 2 18
是这样子的情况嘛?
2 2A 2 20
4 3A 2 18我需要这样的结果
select count(case when xb=1 and nl=20 then null else 1 end) from tb
只排除 同时满足的
select count(case when xb=1 and nl=20 then null else 1 end) from tb这句就可以了
我再问下
同样的条件
我要查 所有的记录 怎么查 ?
(
SELECT 1 XH,1 XB, 18 NL FROM dual
UNION ALL
SELECT 2,2, 20 FROM dual
UNION ALL
SELECT 3,1, 20 FROM dual
UNION ALL
SELECT 4,2, 18 FROM dual
)
SELECT * FROM t WHERE xh NOT IN
(
SELECT xh FROM t WHERE xb=1 AND nl=20
)--result:
1 1 18
2 2 20
4 2 18
(
SELECT 1 XH,1 XB, 18 NL FROM dual
UNION ALL
SELECT 2,2, 20 FROM dual
UNION ALL
SELECT 3,1, 20 FROM dual
UNION ALL
SELECT 4,2, 18 FROM dual
)
SELECT COUNT(1) FROM t WHERE xh NOT IN
(
SELECT xh FROM t WHERE xb=1 AND nl=20
)--RESULT:
3
where xb<>1 and NL<>20orselect * from A
where xb not in(1) and NL not in(20)