mysql - 无法理解 SQL 中的 CASE WHEN 语句

Question

SELECT    state, 
    COUNT(CASE WHEN elevation >= 2000 THEN 1 ELSE NULL END) as count_high_elevation_aiports 
FROM airports 
GROUP BY state;

In the above statement, what is the THEN 1 and what does '1' signify? How does that value '1' after THEN affect the output?

Answer 1

首先，请注意这三个表达式是等价的:

CASE WHEN elevation >= 2000 THEN 1 ELSE NULL END
IF(elevation >= 2000, 1, NULL)
((elevation >= 2000) OR NULL)

如果海拔 >= 2000，表达式的计算结果为“1”，否则表达式的计算结果为 NULL。

“1”通常用作 bool 值 true，您可以将上述表达式中的 MySQL 文字 TRUE 替换为等效结果...但这不是“1”的用途, 在这里。

当与 COUNT() 一起使用时，在这种情况下，1 的唯一真正意义在于它不是 NULL.

这很重要，因为——与流行的看法相反——COUNT() 不计算行数。它计算值。

有什么区别？ NULL 在技术上不是一个值。相反，它是一个标记，表示值不存在，因此COUNT(expr) 仅计算expr 不为空的行。

通过使用像这里这样的表达式，您要求服务器计算 elevation => 2000 的行数，您可以通过给 COUNT() NULL 对于您不希望被计算在内的行...以及您所做的行的非空值。

Aggregate (GROUP BY) functions对值 进行操作——而NULL 也不是这个意义上的值。

另一个使这个基本原理更加清晰的聚合函数是 AVG()。如果您有 3 行……值为 5、NULL 和 10……平均值是多少？如果你说 7.5，那是正确的:这 3 行的平均值是 (5 + 10) ÷ 2 = 5，因为这 3 行只有两个值。 NULL 不是 0，否则平均值将为 (5 + 0 + 10) ÷ 3 = 5，但事实并非如此。

所以，这就是它的工作原理和原因。

How does that value '1' after THEN affect the output?

真的没有。你可以很容易地说 COUNT(CASE WHEN elevation >= 2000 THEN 'cat videos are funny' ELSE NULL END) 因为，就像文字 1 一样，文字string 'cat videos are funny' 也不为 null，并且非 null 值(任何不为 null 的值)都是有效的。

新手可能会尝试使用 COUNT(elevation >= 2000) 来完成此任务，但这会给出错误的答案，因为 elevation < 2000 的行的 0(假)不为空，因此这些行仍会被计算在内。

然后您可能会问，“为什么不直接使用 COUNT(*) ... WHERE elevation >= 2000？”好问题。原因各不相同，但如果您 GROUP BY state 并且没有任何行匹配 WHERE 的状态，这些状态将从结果中完全消除，这通常不是您想要的想。此查询包括它们，计数为零。

---

请注意，顶部的第三个示例表达式 ((elevation >= 2000) OR NULL) 实际上不需要括号。我把它们包括在内是因为这种形式乍一看不一定直观。如果简单地编写 elevation >= 2000 OR NULL，操作的自然优先级将导致它被正确评估。此表达式等同于其他两个表达式，因为 elevation >= 2000 首先计算结果为 1(如果为真)、0(如果为假)或 NULL(如果 elevation 为空)。然后对优先级较低的 OR 求值，得到其中之一:1 OR NULL => 1 ... 0 OR NULL => NULL ... NULL OR NULL => NULL... 在使用 COUNT(elevation >= 2000 OR NULL) 对你来说很自然。