php 带有 LEFT JOIN 的嵌套 SELECT 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12970187/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Nested SELECT statement with LEFT JOIN
提问by Wasim
I can't for the life of me figure out what's wrong with this SQL statement and why it's not producing any results. If I take out the LEFT JOIN is works, so what's wrong with it?
我一生都无法弄清楚这个 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
Would appreciate the help greatly.
非常感谢您的帮助。
回答by John Woo
add the item_idcolumn in your subquery (I guarantee that it will work) so the ONclause can find r.item_id
item_id在您的子查询中添加列（我保证它会起作用），以便ON子句可以找到r.item_id
SELECT item_id, avg(rating) as avg_rating
FROM ratings
GROUP BY item_id
