在mysql查询中使用if条件计数 [英] Count with if condition in mysql query

问题描述

我有两个表，一个用于新闻，另一个用于评论，我想得到其状态已被批准的评论的计数。

I have two tables one is for news and other one is for comments and i want to get the count for 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 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 

这篇关于在mysql查询中使用if条件计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！