php - 如何在 JOIN 语句中指定 WHERE？

Question

这是我第一次尝试 JOIN MySQL 语句...

我有 2 张 table ..games 和 games_ ratings

两个表都有一个 id 列。 id代表游戏的id。我只想获取 rating 列中整数的平均值，其中 games_ ratings 中的 id 等于 games 表中的 id。

SELECT a.id, a.name, AVG(b. rating) ASaverage FROM games a LEFT JOIN games_ ratings b GROUP BY a.id ORDER BYaverage DESC LIMIT 50;

有什么想法吗？

Answer 1

试试这个:

SELECT a.id, a.name, AVG(b.rating) AS average 
    FROM games a 
    LEFT JOIN games_ratings b
    ON a.id = b.id # <-- You need this line I believe 
GROUP BY a.id
ORDER BY average DESC LIMIT 50;

编辑:如果没有完整的架构，这有点困难，但你可以尝试这样的事情。

SELECT a.id, a.name, AVG(b.rating) AS average, COUNT( b.id) as votes
    FROM games a 
    LEFT JOIN games_ratings b
    ON a.id = b.id 
GROUP BY a.id
ORDER BY votes DESC, average DESC LIMIT 50; # <-- You may need to modify this line