虽然题目有点拗口难懂,不知我到底在解决什么问题。我通过例子的方法来说明,就会一目了然了。
最近在做的一个需求,其中一部分要求是:查询出有过什么什么的客户且过滤掉什么的客户,其实就是各种表的联结再加上一些过滤条件。这都不重要,重要的是,这两个过滤条件我写的很有问题。
如下:
假如有一个表tb_ftc(不是真实业务场景的表)
c_id | ch_no | c_st | ... |
01023 | 1 | -1 | |
02345 | 3 | 2 | |
13132 | 2 | 3 | |
23456 | 4 | 5 | |
01023 | 2 | 17 | |
01023 | 10 | 8 | |
23456 | 1 | 2 | |
13132 | 3 | 7 | |
23456 | 10 | 5 | |
... | ... |
过滤条件1:剔除c_st=2,5的c_id;
过滤条件2:取ch_no有过1和2的记录;
过滤条件3:剔除ch_no=10的c_id。
我写的第一版:
select c_id,...
from tb_ftc
where c_st not in (2,5)
and ch_no in (1,2)
and ch_no!=10;
因为查出来的结果,即c_id要和其他表进行关联,然后发现,ch_no=10的c_id还是存在的。
原因是 ch_no in (1,2)这个条件执行了,但ch_no!=10没执行。也就是说,有过ch_no=1和ch_no=2的c_id被留下了,这个c_id要和其他表关联
c_id | ch_no | c_st | ... |
01023 | 1 | -1 | |
13132 | 2 | 3 | |
01023 | 2 | 17 | |
23456 | 1 | 2 | |
... | ... |
实际上c_id=01023&23456通过c_id和其他表关联之后,ch_no=10还是存在的
01023 | 10 | 8 | |
23456 | 10 | 5 |
于是有了第二版:
当时的思路是,既然第二个过滤条件没用上,那就把ch_no!=10的记录单独作为一个表与过滤前两个条件的表进行关联,于是有了下面这样的代码
select a.c_id,a.ch_no,b.ch_no
from
(select c_id,ch_no
from tb_ftc
where c_st not in (2,5)
and ch_no in (1,2)) a join
(select c_id,ch_no
from tb_ftc
where ch_no!=10) b
on a.c_id=b.c_id;
这版写完之后逻辑感觉完全没有问题
c_id | ch_no |
01023 | 1 |
13132 | 2 |
01023 | 2 |
... |
c_id | ch_no |
01023 | 1 |
02345 | 3 |
13132 | 2 |
23456 | 4 |
01023 | 2 |
23456 | 1 |
13132 | 3 |
... |
join得到:
c_id | a.ch_no | b.ch_no |
01023 | 1 | 1 |
01023 | 1 | 2 |
13132 | 2 | 2 |
13132 | 2 | 3 |
01023 | 2 | 1 |
01023 | 2 | 2 |
... |
去重:
c_id | a.ch_no |
01023 | 1 |
13132 | 2 |
01023 | 2 |
然而,用c_id=01023、13132去关联其他表,ch_no=10的这个c_id还是存在即01023。
我理解的偏差就在,以为删掉了ch_no=10的c_id,但只是删掉了tb_ftc表中的c_id,忽略了一个c_id有多个ch_no。
后来仔细想了一下,这个问题的思路应该是,只要有ch_no=10的c_id,就直接删掉,即使这个c_id有ch_no=1&2的记录。
所以,首先将ch_no=10的c_id全部挑选出来,然后再从tb_ftc表中挑选不是ch_no=10的c_id,加上另外两个条件即可。
select c_id
from tb_tfc
where c_id not in(select c_id
from tb_tfc
where ch_no=10)
and ch_no in(1,2)
and c_st not in (2,5)
但是 not in (select 子句)通常可以由left join 来替换,联结比子查询更高效。
所以最终版本是:
select a.c_id
from tb_tfc a left join
(select c_id
from tb_tfc
where ch_no=10) b
on a.c_id=b.c_id
where b.c_id is null
and ch_no in(1,2)
and c_st not in (2,5)
left join 查询精髓:查询出a表有但b表没有的记录
select a.id
from tb a
left join (select id,no
from tb
where no=1) b
on a.id=b.id
where b.id is null;
其实对于熟悉sql的人说就是个小问题,但是对于一个从没写过复杂sql的我,各种联结子查询我就晕了。。。