MYSQL 查询 : USING LEFT JOIN

Question

SELECT jos_mod_gdwcomm.*, COUNT(jos_mod_gdwcomm_spam.reportid) `num_spam` 
FROM `jos_mod_gdwcomm` 
WHERE `num_spam` > 0 
LEFT JOIN `jos_mod_gdwcomm_spam` 
ON (jos_mod_gdwcomm.id = jos_mod_gdwcomm_spam.commid) 
GROUP BY jos_mod_gdwcomm.id
ORDER BY `num_spam` DESC, jos_mod_gdwcomm_spam.datetime DESC

我如何修改此查询，使其仅返回 num_spam 大于 0 的行。

我试过

WHERE `num_spam` > 0

在一些地方，但运气不好。

谢谢

Answer 1

你需要一个 HAVING 子句:

SELECT jos_mod_gdwcomm.*, COUNT(jos_mod_gdwcomm_spam.reportid) `num_spam` 
FROM `jos_mod_gdwcomm` 
LEFT JOIN `jos_mod_gdwcomm_spam` 
ON (jos_mod_gdwcomm.id = jos_mod_gdwcomm_spam.commid) 
GROUP BY jos_mod_gdwcomm.id

HAVING num_spam > 0

ORDER BY `num_spam` DESC, jos_mod_gdwcomm_spam.datetime DESC