MySQL:如何检查每个组的哪些条目的值大于其相应组中的平均值？

Question

我需要显示每个组(由 GROUP BY 组成)的值，该组的值大于其所属组的平均值。我尝试这样做:

WHERE price > (SELECT AVG(I.price)
               FROM items I, category C
               WHERE I.itemcode = C.itemcode
               GROUP BY C.category) 

但它返回多行，因此不能与“>”运算符一起使用。也就是说，我该怎么做呢？提前致谢!

编辑:这是完整的查询

SELECT C.category, I.itemname, I.price
FROM item I INNER JOIN category C on I.itemcode = C.itemcode
WHERE I.price > (SELECT AVG(I.price) FROM items I, category C WHERE I.itemcode = C.itemcode GROUP BY C.category)
ORDER BY 1, 2;

Answer 1

您需要的是相关查询。

在主查询中为类别表设置别名，然后在子查询中使用该别名。

SELECT C.category, I.itemname, I.price 
FROM item I INNER JOIN category C on I.itemcode = C.itemcode 
WHERE I.price > (SELECT AVG(Itm.price) FROM items Itm, category Cat 
           WHERE Itm.itemcode = Cat.itemcode 
           and Cat.cateogry = C.category) 
ORDER BY 1, 2;

神奇之处在于Cat.category = C.category C是主查询中的Cateogry表，而Cat是子查询中的相同Cateogry表。

因此，它将计算与主查询中查询的商品具有相同类别的商品的平均商品价格。

请记住，这对性能不友好，因为它可能会计算主查询中每条记录的平均价格。所以要小心。

更好的方法是使用 @Summer-Sky 指出的连接