在 MySQL 查询中使用 IF 条件进行计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9798937/
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 with IF condition in MySQL query
提问by user1163513
I have two tables, one is for news and the other one is for comments and I want to get the count of the comments whose status has been set as approved.
我有两张表，一张用于新闻，另一张用于评论，我想获取状态已设置为已批准的评论数。
SELECT
ccc_news . *,
count(if(ccc_news_comments.id = 'approved', ccc_news_comments.id, 0)) AS comments
FROM
ccc_news
LEFT JOIN
ccc_news_comments
ON ccc_news_comments.news_id = ccc_news.news_id
WHERE
`ccc_news`.`category` = 'news_layer2'
AND `ccc_news`.`status` = 'Active'
GROUP BY
ccc_news.news_id
ORDER BY
ccc_news.set_order ASC
LIMIT 20
But the problem with this query is that the minimum value that is fetched for the comments column is 1 whether there is any comment existent corresponding to that news or not.
但是此查询的问题在于，无论是否存在与该新闻对应的任何评论，为评论列获取的最小值都是 1。
Any help would be highly appreciable.
任何帮助将是非常可观的。
回答by
Use sum()in place of count()
sum()代替使用count()
Try below:
试试下面：
SELECT
ccc_news . * ,
SUM(if(ccc_news_comments.id = 'approved', 1, 0)) AS comments
FROM
ccc_news
LEFT JOIN
ccc_news_comments
ON
ccc_news_comments.news_id = ccc_news.news_id
WHERE
`ccc_news`.`category` = 'news_layer2'
AND `ccc_news`.`status` = 'Active'
GROUP BY
ccc_news.news_id
ORDER BY
ccc_news.set_order ASC
LIMIT 20
回答by mojuba
Better still (or shorter anyway):
更好（或更短）：
SUM(ccc_news_comments.id = 'approved')
This works since the Boolean type in MySQL is represented as INT0and 1, just like in C. (May not be portable across DB systems though.)
这是可行的，因为 MySQL 中的 Boolean 类型表示为INT0and 1，就像在 C 中一样。（尽管可能无法跨数据库系统移植。）
As for COALESCE()as mentioned in other answers, many language APIs automatically convert NULLto ''when fetching the value. For example with PHP's mysqliinterface it would be safe to run your query without COALESCE().
至于COALESCE()其他答案中提到的，许多语言 API在获取值时会自动转换NULL为''。例如，使用 PHP 的mysqli界面，在没有COALESCE().
回答by Edemilson Lima
This should work:
这应该有效：
count(if(ccc_news_comments.id = 'approved', ccc_news_comments.id, NULL))
count()only check if the value exists or not. 0 is equivalent to an existent value, so it counts one more, while NULL is like a non-existent value, so is not counted.
count()只检查值是否存在。0 相当于一个存在的值，所以它再计数一个，而 NULL 就像一个不存在的值，所以不计数。
回答by Mosty Mostacho
Replace this line:
替换这一行：
count(if(ccc_news_comments.id = 'approved', ccc_news_comments.id, 0)) AS comments
With this one:
有了这个：
coalesce(sum(ccc_news_comments.id = 'approved'), 0) comments
