我有一个使用MySQL的问题。我有 2 张桌子,一张有游戏名称,另一张有不同的游戏评级。我希望能够按名称搜索游戏并查看每个游戏的平均评分。到目前为止,我设法获得了平均评分,但我只会看到 1 场比赛的评分,而不是多个条目。这是我到目前为止的查询:
SELECT games.name, CAST(AVG(reviews.rating) as decimal(6, 1)) AS average_rating
FROM games
JOIN reviews
ON games.id = reviews.game
WHERE games.name LIKE '%spider%'
AND type = '7'
AND rating != 0
我希望你们中的一些聪明人可以帮助我解决这个问题!谢谢,罗伯特·
您必须在正确的字段上使用 GROUP BY
子句来获取每个组的平均值,否则它将计算所有行的平均值。我想这是games.id
,但取决于您的表模式。
SELECT games.name, CAST(AVG(reviews.rating) as decimal(6, 1)) AS average_rating
FROM games
JOIN reviews
ON games.id = reviews.game
WHERE games.name LIKE '%spider%'
AND type = '7'
AND rating != 0
GROUP BY games.id;
阅读有关所谓的聚合函数的更多信息
您必须使用GROUP BY games.name
来分隔不同的名称。问题是AVG
是一个折叠结果的汇总函数。GROUP BY
将结果分解。
SELECT games.name, CAST(AVG(reviews.rating) as decimal(6, 1)) AS average_rating
FROM games
JOIN reviews ON games.id = reviews.game
WHERE games.name LIKE '%spider%' AND type = '7' AND rating != 0
GROUP BY games.name