mySQL 相关子查询

2022-01-23 00:00:00 join subquery mysql correlated-subquery

正在尝试编写一个 mysql 查询，但遇到了很多困难.

trying to write a mysql query and having a lot of difficult with this one.

我有两个表(Item:关于项目的信息，以及 itemReview:对项目的评论)

I have two tables( Item: info about items, and itemReview: reviews for the items )

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

What I would like to do is select all the items that belong to a particular location (which is what my outer query does) and then for each item in the outer query, get the average of all the rating fields in the itemReview table

这是我的尝试:

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'

but mySQL says: Unknown column 'Item.idItem' in 'where clause'

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

Any help would be very appreciated!! thanks!!

推荐答案

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

You are trying to access the Item.idItem inside of the subquery but it is not available there. You should use something like this:

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';

相关文章