了解MySQL中的SUM(NULL) [英] Understanding SUM(NULL) in MySQL

问题描述

通常在任何方程式中包含NULL时，整个结果都分解为NULL(例如SELECT 2 + NULL + 5返回NULL)

Usually when NULL is involved in any equation then the whole result resolves into NULL (e.g. SELECT 2 + NULL + 5 returns NULL)

在以下情况下相同:

SELECT SUM(NULL)返回NULL. Proposition #1

使用SUM聚合列并且该列也可以包含NULL值时会发生什么?

What happens when SUM is used to aggregate a column and the column can contain NULL values too ?

基于proposition #1为什么输出不会导致NULL.

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;

输出: 100

但是我期待着NULL.

在这种情况下，MySQL是否会默默地跳过那些NULL值?

http://sqlfiddle.com/#!9/3f99bb/2

推荐答案

SUM([DISTINCT] expr)
返回expr的总和.如果返回集没有行，则SUM()返回 空值. DISTINCT关键字只能用于对不同的值求和 的

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()返回NULL.

SUM() returns NULL if there were no matching rows.

此外，还说:

本节介绍了在其上运行的组(汇总)功能 值集.除非另有说明，否则组函数将忽略NULL 值.

This section describes group (aggregate) functions that operate on sets of values. Unless otherwise stated, group functions ignore NULL values.

换句话说，SUM的行为是这样的，因为这就是它的定义方式.

in other words SUM behaves like this because that's the way it's defined to be.

这篇关于了解MySQL中的SUM(NULL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！