大家帮我分析分析这三条语句 用in和not in统计的数目为什么不是总和
SQL> select count(*) from rkb where fl='1' and jldw in (select jldw from rkb where fl='0'); COUNT(*)
----------
92SQL> select count(*) from rkb where fl='1' and jldw not in (select jldw from rkb where fl='0'); COUNT(*)
----------
0SQL> select count(*) from rkb where fl='1'; COUNT(*)
----------
201
SQL> select count(*) from rkb where fl='1' and jldw in (select jldw from rkb where fl='0'); COUNT(*)
----------
92SQL> select count(*) from rkb where fl='1' and jldw not in (select jldw from rkb where fl='0'); COUNT(*)
----------
0SQL> select count(*) from rkb where fl='1'; COUNT(*)
----------
201
select jldw from rkb where fl='0'肯定有201-0-91=110个NULL
----------
46SQL> select count(*) from rkb where fl='0' and jldw is null; COUNT(*)
----------
71
46条呀 怎么会连fl='0' 的算上才是总数那也应该是总数是下边三个的和呀
SQL> select count(*) from rkb where fl='1'; COUNT(*)
----------
201
SQL> select count(*) from rkb where fl='1' and jldw not in (select jldw from rkb where fl='0'); COUNT(*)
----------
0SQL> select count(*) from rkb where fl='1' and jldw in (select jldw from rkb where fl='0'); COUNT(*)
----------
92SQL> select count(*) from rkb where fl='0' and jldw is null; COUNT(*)
----------
71
----------
0
是这句话出了问题,not in 如果里面有空值的话,全部返回null,所以你这得到0,
select count(*) from rkb where fl='1' and jldw not in (select jldw from rkb where fl='0' and jldw is not null);试试
这样对了