使用HAVING with another record

Question

我正在尝试计算另一个表上的行数，并使用必须仅选择大于0的行。

SELECT COUNT(t3.ID),t1.ID,t2.sell,t1.date
FROM `album` t1 
INNER JOIN `members` t2 ON t2.aID = t1.ID 
INNER JOIN `table` t3 ON t3.rID = t1.ID 
WHERE t1.date <= '$dt' AND t2.sell = '1'
ORDER BY t1.date DESC
HAVING COUNT(t3.ID) > 0

它不起作用。我哪里错了？谢谢

Answer 1

在使用诸如COUNT之类的聚合函数时，您需要一个GROUP BY子句，以便MySQL知道如何对数据进行分组。

SELECT COUNT(t3.ID),t1.ID,t2.sell,t1.date
FROM `album` t1 
INNER JOIN `members` t2 ON t2.aID = t1.ID 
INNER JOIN `table` t3 ON t3.rID = t1.ID 
WHERE t1.date <= '$dt' AND t2.sell = '1'
GROUP BY t1.ID,t2.sell,t1.date
HAVING COUNT(t3.ID) > 0
ORDER BY t1.date DESC

有关详细信息，请参阅this。