php - 带有 LEFT JOIN 的嵌套 SELECT 语句

Question

我这辈子都搞不清楚这个 SQL 语句有什么问题，为什么它没有产生任何结果。如果我去掉 LEFT JOIN 是可行的，那么它有什么问题吗？

SELECT  b.id, r.avg_rating
FROM    items AS b
LEFT JOIN
(
    SELECT avg(rating) as avg_rating
    FROM ratings 
    GROUP BY item_id
) AS r 
  ON b.id = r.item_id
WHERE b.creator = " . $user_id . " 
AND b.active = 1
AND b.deleted = 0
ORDER BY b.order ASC, b.added DESC

非常感谢您的帮助。

Answer 1

在您的子查询中添加 item_id 列(我保证它会工作)以便 ON 子句可以找到 r。 item_id

SELECT item_id, avg(rating) as avg_rating
FROM ratings 
GROUP BY item_id