MySQL:使用 COUNT 和 GROUP BY 进行查询时的 WHERE 子句？

Question

对于我的广告所使用的每个类别，我想显示广告的总数。

我当前的查询如下所示:

SELECT
    categories_adverts.category,
    COUNT(adverts.id) AS numberOfAdverts
FROM adverts
LEFT JOIN categories_adverts
ON adverts.id_categories_adverts=categories_adverts.id
GROUP BY category ASC

我正在努力添加一个 WHERE 子句，该子句指定仅显示那些持续时间仍然有效的广告。我尝试在“FROM adverts”和“LEFT JOIN ...”之间包含以下子句:

WHERE TIME_TO_SEC(timediff(now(),adverts.stamp_created))<=adverts.duration

所以整个查询就像这样，但这不起作用:

SELECT
    categories_adverts.category,
    COUNT(adverts.id) AS numberOfAdverts
FROM adverts
WHERE TIME_TO_SEC(timediff(now(),adverts.stamp_created))<=adverts.duration
LEFT JOIN categories_adverts
ON adverts.id_categories_adverts=categories_adverts.id
GROUP BY category ASC

查询有什么问题？

Answer 1

您的 WHERE 子句应该位于连接之后、分组和排序之前，像这样:

SELECT
    categories_adverts.category,
    COUNT(adverts.id) AS numberOfAdverts
FROM adverts
LEFT JOIN categories_adverts
ON adverts.id_categories_adverts=categories_adverts.id
WHERE TIME_TO_SEC(timediff(now(),adverts.stamp_created))<=adverts.duration
GROUP BY category ASC