实验 oracle sum null值相关

今天有同事问我sum函数 null值的问题。只记得在分析求和的时候是不计算null值（Oracle中的组函数忽略空值），其它的到没有深入了解过。乘此良机，稍微研究下，记录之~

SQL 采用一种三值逻辑来操作，Oracle 则是按ANSI SQL 的要求来实现NULL 值。基于这些规则的要求，x 与NULL 的比较结果既不为true 也不为false，也就是说，实际上，它是未知的（unknown）。

select * from dual where null=null;

no rows selected

select * from dual where null <> null;

no rows selected

select * from dual where null is null;

D
-
X

z@test10gr2> select sum(null) from dual;

SUM(NULL)
----------

z@test10gr2>  select sum(null+33) from dual;

SUM(NULL+33)
------------

z@test10gr2>  select nvl(sum(null+33),99999) from dual;

NVL(SUM(NULL+33),99999)
-----------------------
99999

z@test10gr2>  select sum(null+null) from dual;

SUM(NULL+NULL)
--------------

z@test10gr2> select nvl(sum(null+null),9999) from dual;

NVL(SUM(NULL+NULL),9999)
------------------------
9999

z@test10gr2> create table t_null(a number);

Table created.

z@test10gr2> insert into t_null  values(null);

1 row created.

z@test10gr2> insert into t_null  values(null);

1 row created.

z@test10gr2> insert into t_null  values(null);

1 row created.

z@test10gr2> select rownum,a from t_null;

ROWNUM          A
---------- ----------
1
2
3

z@test10gr2> select sum(a) from t_null;

SUM(A)
----------

z@test10gr2> select nvl(sum(a),0) from t_null;

NVL(SUM(A),0)
-------------
0

z@test10gr2>  insert into t_null  values(77);

1 row created.

z@test10gr2> select rownum,a from t_null;

ROWNUM          A
---------- ----------
1
2
3
4         77

z@test10gr2> select sum(a) from t_null;

SUM(A)
----------
77

正如之前所说，组函数不计算null值。

z@test10gr2>select * from v\$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

• 博文量
182
• 访问量
356667