用in和not in统计的数目为什么不是总和
大家帮我分析分析这三条语句 用in和not in统计的数目为什么不是总和
SQL> select count(*) from rkb where fl= '1 ' and jldw in (select jldw from rkb where fl= '0 ');
COUNT(*)
----------
92
SQL> select count(*) from rkb where fl= '1 ' and jldw not in (select jldw from rkb where fl= '0 ');
COUNT(*)
----------
0
SQL> select count(*) from rkb where fl= '1 ';
COUNT(*)
----------
201
[解决办法]
NULL不包含在IN与NOT IN中
select jldw from rkb where fl= '0 '肯定有201-0-91=110个NULL
[解决办法]
select count(*) from rkb where fl= '1 ' and jldw not in (select jldw from rkb where fl= '0 ');
COUNT(*)
----------
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);
试试
