mysql sum null_了解MySQL中的SUM（NULL）
Usually when NULL is involved in any equation then the whole result resolves into NULL (e.g. SELECT 2 + NULL + 5 returns NULL)
Same holds for the following case:
SELECT SUM(NULL) returns NULL. Proposition #1
What happens when SUM is used to aggregate a column and the column can contain NULL values too ?
Based on the proposition #1 why the output doesn’t result in NULL.
CREATE TABLE t (age INT NULL);
INSERT INTO t (age) VALUES (15),(20), (NULL), (30), (35);
SELECT
SUM(age)
FROM t;
Output: 100
But I was expecting NULL.
Does MySQL silently skips those NULL values in this case?
解决方案
Well it’s explained in the manual
SUM([DISTINCT] expr)
Returns the sum of expr. If the return set has no rows, SUM() returns
NULL. The DISTINCT keyword can be used to sum only the distinct values
of expr.
SUM() returns NULL if there were no matching rows.
What’s more it’s also said that:
This section describes group (aggregate) functions that operate on
sets of values. Unless otherwise stated, group functions ignore NULL
values.
in other words SUM behaves like this because that’s the way it’s defined to be.
我不是女神ヾ
我会带着你远行
朱雀
不念不忘少年蓝@
柔情只为你懂
小灰灰
还没有评论，来说两句吧...