日期:2014-05-17  浏览次数:21514 次

用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);

试试