问题描述

遇到空值时，请解释 SUM 函数在 Oracle 中的内部功能，我们将不胜感激:

It would be appreciated explaining the internal functionality of SUM function in Oracle, when encountering null values:
The result of

select sum(null) from dual;
is null

但是当一个空值在一个值序列中时(比如一个可为空的列的总和)，空值的计算值为0

But when a null value is in a sequence of values (like sum of a null-able column), the calculated value of null value will be 0

select sum(value) from
(
select case when mod(level , 2) = 0 then null else level end as value from dual
connect by level <= 10
)
is 25

看到

select (1 + null) from dual
is null

因为任何带有 null 的操作都会导致 null(除了 is null 运算符).

==========================
由于评论而有所更新:

As any operation with null will result null (except is null operator).

==========================
Some update due to comments:

create table odd_table as select sum(null) as some_name from dual;

将导致:

create table ODD_TABLE
(
  some_name NUMBER
)

为什么 some_name 列是 number 类型?

Why some_name column is of type number?

推荐答案

SQL 在计算 SUM 时不会将 NULL 值视为零，它会忽略它们:

SQL does not treat NULL values as zeros when calculating SUM, it ignores them:

返回表达式中所有值的总和，或仅返回 DISTINCT 值.空值被忽略.

Returns the sum of all the values, or only the DISTINCT values, in the expression. Null values are ignored.

这仅在一种情况下有所不同 - 当被累加的序列不包含数字项时，只有 NULLs:如果至少存在一个数字，则结果将是数字.

This makes a difference only in one case - when the sequence being totalled up does not contain numeric items, only NULLs: if at least one number is present, the result is going to be numeric.

这篇关于为什么 SUM(null) 在 Oracle 中不为 0?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！