mysql - 在 COUNT(*) 条件 MySQL 中添加一个 WHERE

Question

我有一个 SQL 查询，它检索与广告表连接的评级表的平均评级。

我的查询工作正常，但我想添加一个 WHERE 语句以仅获取获得两个或更多评级的广告。

这是我的查询:

SELECT ad_id, AVG(rating) as average_rating,
COUNT(user_id) as num_rates
FROM ads_rating
GROUP BY ad_id

我想要的查询:

SELECT ad_id, AVG(rating) as average_rating,
COUNT(user_id) as num_rates
FROM ads_rating
WHERE num_rates > 1
GROUP BY ad_id

但是 MariaDB 告诉我:
[错误] 1054 - “where 子句”中的未知列“num_rates”

谢谢，对不起我的 MySQL 菜鸟

Answer 1

您不能在 WHERE 中使用列别名所以你需要做。

SELECT ad_id, AVG(rating) as average_rating,
COUNT(user_id) as num_rates
FROM ads_rating
WHERE COUNT(user_id) > 1
GROUP BY ad_id

或者更好地使用 HAVING 与 GROUP BY 一起使用

SELECT ad_id, AVG(rating) as average_rating,
COUNT(user_id) as num_rates
FROM ads_rating
GROUP BY ad_id
HAVING COUNT(user_id) > 1