为什么 SUM(null) 在 Oracle 中不为 0?
遇到空值时，请解释 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
DISTINCTvalues, 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.
相关文章