MySQL 中的 COUNT CASE 和 WHEN 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5045124/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
COUNT CASE and WHEN statement in MySQL
提问by Ivan Tanasijevic
How to use COUNT CASE and WHEN statement in MySQL query, to count when data is NULL and when it is not NULL in one MySQL query?
如何在 MySQL 查询中使用 COUNT CASE 和 WHEN 语句，在一个 MySQL 查询中计算数据何时为 NULL 和何时不为 NULL？
回答by OMG Ponies
Use:
用：
SELECT SUM(CASE
WHEN t.your_column IS NULL THEN 1
ELSE 0
END) AS numNull,
SUM(CASE
WHEN t.your_column IS NOT NULL THEN 1
ELSE 0
END) AS numNotNull
FROM YOUR_TABLE t
That will sum up the column NULL & not NULL for the entire table. It's likely you need a GROUP BY clause, depending on needs.
这将总结整个表的 NULL & not NULL 列。根据需要，您可能需要一个 GROUP BY 子句。
回答by Daniels118
You can exploit the fact that COUNT only counts non-null values:
您可以利用 COUNT 只计算非空值的事实：
SELECT COUNT(IFNULL(t.your_column, 1)) AS numNull,
COUNT(t.your_column) AS numNotNull
FROM YOUR_TABLE t
Another approach is to use the fact that logical conditions get evaluated to numeric 0 and 1, so this will also work:
另一种方法是利用逻辑条件被评估为数字 0 和 1 的事实，因此这也适用：
SELECT IFNULL(SUM(t.your_column IS NULL), 0) AS numNull,
IFNULL(SUM(t.your_column IS NOT NULL), 0) AS numNotNull
FROM YOUR_TABLE t
Please note that SUM will return NULL if there are no rows selected (i.e. the table is empty or a where condition excludes all rows), this is the reason for the IFNULL statements.
请注意，如果没有选择任何行（即表为空或 where 条件排除所有行），SUM 将返回 NULL，这就是 IFNULL 语句的原因。
