大家帮我分析分析这三条语句 用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
解决方案 »
- 关于oracle转移的问题
- oracle 10g手工创建数据库
- Oracle自定议聚合拼接string,同时合并两列时顺序不一样
- 请问oracle apex的问题去那里问比较好?
- ORA-01756: quoted string not properly terminated 待牛人 解答
- oracle clob 字节数 问题 急
- 超级傻B的oracle问题
- 请教SQL*PLUS连接的问题
- 在linux72下安装oracle9i,停留在init java virtual machine,求救
- 大型ORACLE数据库应用的讨论,欢迎高手提出见解
- 关于函数中不能UPDATE的问题.
- 大量数据从A表转移到B表的存储过程,使用事务。
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);试试
这样对了