MySQL相关子查询

问题描述

试图编写一个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';

这篇关于MySQL相关子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！