Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0
Connected as bibase
SQL> create table a
2 ( achar(1),
3bchar(2)
4);
Table created
SQL> insert into a values (1,2);
1 row inserted
SQL>insert into a values ('',3);
1 row inserted
SQL> insert into a values ('','');
1 row inserted
SQL> select count(*) from a;
COUNT(*)
----------
3
SQL>select count(*) from a where not exists (select null from a where a=1);
COUNT(*)
----------
0
SQL>select count(*) from a where a1;
COUNT(*)
----------
0
SQL>update a set a=''; --关键点就在与此
3 rows updated
SQL> commit;
Commit complete
SQL> select * from a;
A B
- --
2
3
区别点如下（都是现实A为空的记录数）：
SQL>select count(*) from a where not exists (select null from a where a=1);
COUNT(*)
----------
3
SQL> select count(*) from a where a1;
COUNT(*)
----------
0
SQL> select * fromv$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE
9.2.0.8.0
Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
原帖由 newkid 于 2010-5-28 22:40 发表
说到底还是NULL的原因。
对。但是发现没有
我insert的时候与update之处。一直没有明白
SQL>insert into a values ('',3);
1 row inserted
SQL> insert into a values ('','');
SQL>select count(*) from a where not exists (select null from a where a=1);
COUNT(*)
----------
0
SQL>select count(*) from a where a1;
COUNT(*)
----------
0
当我进行update 后以上两句就有变化了
SQL>update a set a=''; --关键点就在与此
3 rows updated
SQL> commit;
Commit complete
SQL> select * from a;
A B
- --
2
3
区别点如下（都是现实A为空的记录数）：
SQL>select count(*) from a where not exists (select null from a where a=1);
COUNT(*)
----------
3
SQL> select count(*) from a where a1;
COUNT(*)
----------
0
不还是NULL的问题嘛！
注意not exists,你写的是非相关子查询，只要子查询结果集有，也就是为真，那么就返回0，否则返回全部，也就是解释了在update之前是0，update之后是3
select count(*) from a where not exists (select null from a where a=1);
-- a1两次都为0，一点都不奇怪了，因为事实如此
select count(*) from a where a1;
update a set a='';
原帖由 dingjun123 于 2010-5-29 00:28 发表
不还是NULL的问题嘛！
注意not exists,你写的是非相关子查询，只要子查询结果集有，也就是为真，那么就返回0，否则返回全部，也就是解释了在update之前是0，update之后是3
select count(*) from a where not exists (select null from a where a=1);
-- a1两次都为0，一点都不奇怪了，因为事实如此
select count(*) from a where a1;
update a set a=''; 正解
在update a set a='';之前,子查询select null from a where a=1有值,
[ 本帖最后由 ruideliang 于 2010-6-14 20:41 编辑 ]
