在 MySQL 查询中使用 IF 条件进行计数
我有两张表,一张是新闻,一张是评论,我想获取状态已设置为已批准的评论数.
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
但是这个查询的问题在于,无论是否存在与该新闻对应的任何评论,为评论列获取的最小值都是 1.
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.
任何帮助都将受到高度赞赏.
Any help would be highly appreciable.
推荐答案
使用 sum()
代替 count()
试试下面:
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
相关文章