mySQL相关子查询

Question

尝试编写一个mysql查询，并对此有很多困难。

我有两个表( Item: items about items和itemReview: reviews for the items)

我想要做的是选择属于特定位置的所有项目(这就是我的外部查询所做的工作)，然后对于外部查询中的每个项目，获取itemReview表中所有评级字段的平均值

这是我的尝试：

SELECT 
    Item.idDish, 
    Item.dateAdded, 
    Item.dateModified, 
    Item.fidLocation, 
    Item.category, 
    Item.description, 
    Item.price, 
    Item.name, 
    Item.fullImageName, 
    Item.thumbnailImageName, 
    sub.rating
FROM Item 
JOIN (
        SELECT 
            AVG(ItemReview.rating) AS rating 
        FROM ItemReview 
        WHERE ItemReview.fidItem = Item.idItem
    ) AS sub
WHERE Item.fidLocation = '63';

但是mySQL说:where子句中的未知列'Item.idItem‘

任何帮助都将不胜感激！谢谢！！

Answer 1

您正在尝试访问子查询中的Item.idItem，但它在那里不可用。你应该使用类似这样的东西：

SELECT 
    Item.idDish, 
    Item.dateAdded, 
    Item.dateModified, 
    Item.fidLocation, 
    Item.category, 
    Item.description, 
    Item.price, 
    Item.name, 
    Item.fullImageName, 
    Item.thumbnailImageName, 
    sub.rating
FROM Item 
JOIN 
(
    SELECT fidItem, AVG(ItemReview.rating) AS rating 
    FROM ItemReview 
    GROUP BY ItemReview.fidItem
) AS sub
   ON sub.fidItem = Item.idItem
WHERE Item.fidLocation = '63';

Answer 2

查询：

SELECT 
    Item.idDish, 
    Item.dateAdded, 
    Item.dateModified, 
    Item.fidLocation, 
    Item.category, 
    Item.description, 
    Item.price, 
    Item.name, 
    Item.fullImageName, 
    Item.thumbnailImageName, 
    (   SELECT 
            AVG(ItemReview.rating) AS rating 
        FROM ItemReview 
        WHERE ItemReview.fidItem = Item.idItem
    ) AS rating
FROM Item 
WHERE Item.fidLocation = '63'

Answer 3

试试这个：

SELECT i.idDish, i.dateAdded, i.dateModified, i.fidLocation, i.category, 
       i.description, i.price, i.name, i.fullImageName, 
       i.thumbnailImageName, ir.rating 
FROM Item i 
LEFT JOIN (SELECT fiditem, AVG(ItemReview.rating) AS rating 
           FROM ItemReview
           GROUP BY fiditem) ir ON ir.fidItem = i.idItem 
WHERE i.fidLocation = '63';