总和，平均，最大，最小，空值计数 [英] Sum, Avg, Max, Min, Count of NULL values

问题描述

在MySQL中，SELECT 2+NULL FROM tbl_name将返回NULL，因为MySQL无法将NULL解释为数字.

In MySQL SELECT 2+NULL FROM tbl_name will return NULL, since MySQL cannot interpret NULL as a number.

但是，如果只有一个值是NULL，为什么SELECT SUM(quantity) FROM tbl_name不返回NULL?对于MIN，MAX，AVG等也是如此.由于MySQL不知道NULL是什么，所以它不应该为所有指定的函数返回NULL吗?

But why will SELECT SUM(quantity) FROM tbl_name not return NULL if just one of the values is NULL? The same goes for MIN, MAX, AVG, etc. Since MySQL doesn't know what NULL could be, shouldn't it return NULL for all the specified functions?

推荐答案

这是一个很好的问题，但没有很好的答案.您的两个示例中对NULL的处理方式是不同的.

This is a good question, and one that does not have a good answer. The treatment of NULL in your two examples is different.

基本问题是NULL的含义.通常，它用于表示 missing 值.但是，在ANSI标准中，它代表 unknown 值.我确信哲学家可以将书目专门放在遗漏"和未知"之间的区别上.

The fundamental problem is what NULL means. Commonly, it is used to denote missing values. However, in the ANSI standard, it stands for an unknown value. I'm sure philosophers could devote tomes to the difference between "missing" and "unknown".

在一个简单的表达式(布尔，算术或另一种标量)中，ANSI在几乎所有操作数都是未知"的情况下都定义了未知"的结果.有一些例外:NULL AND FALSE为false和NULL IS NULL为true，但这很少见.

In a simple expression (boolean or arithmetic or scalar of another sort), ANSI defines the result of "unknown" in almost all cases where any of the operands are "unknown". There are some exceptions: NULL AND FALSE is false and NULL IS NULL is true, but these are rare.

对于聚合操作，将SUM()视为所有已知值之和"，依此类推. SUM()与+不同对待NULL值.但是，这种行为也是标准行为，因此所有数据库都是这样工作的.

For the aggregation operations, think of SUM() as "sum all the known values", and so on. SUM() treats NULL values differently from +. But, this behavior is also standard so that is how all databases work.

如果当任何操作数为NULL时想要聚合的NULL值，则需要使用CASE.我认为单列的最简单方法是:

If you want a NULL value for an aggregation when any of its operands is NULL, then you need to use CASE. I think the easiest way for a single column is:

(CASE WHEN COUNT(col) = COUNT(*) THEN SUM(COL) END)

这篇关于总和，平均，最大，最小，空值计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！