怎么进行2个既不满足A又不满足B条件的查询?
比如我有个表A
A
XH XM XB NL
1 1A 1 18
2 2A 2 20
3 3A 1 20
4 3A 2 18
.............
怎么查询 XB不为1同时NL不为20 的合计条目数???
[解决办法]
WITH t AS(SELECT 1 XH,1 XB, 18 NL FROM dualUNION ALLSELECT 2,2, 20 FROM dualUNION ALLSELECT 3,1, 20 FROM dualUNION ALLSELECT 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)