mysql - 子查询为 2 个几乎相同的查询返回不同的值

Question

我对以下查询及其返回值感到困惑。为什么当我定义 recipe r 时它只返回一个值 在子查询中，但是当我定义 recipe r 时返回 20 个值 在主查询中？造成差异的原因是什么？

第一个查询:

SELECT   pizza, ingredient, amount 
FROM     recipe 
WHERE    amount = 
         (      SELECT Max(amount) 
                FROM   recipe r
                WHERE  ingredient=r.ingredient) 
ORDER BY ingredient;

  pizza  | ingredient | amount  
---------+------------+--------  
 seafood | seafood    |    200

第二个查询:

SELECT   pizza, ingredient, amount 
FROM     recipe r 
WHERE    amount= 
         (      SELECT max(amount) 
                FROM   recipe 
                WHERE  ingredient=r.ingredient) 
ORDER BY ingredient;

   pizza    | ingredient | amount  
------------+------------+--------   
 napolitana | anchovies  |    100
 special    | bacon      |     25
 cabanossi  | cabanossi  |    150
 siciliano  | capsicum   |     75
 mexicano   | capsicum   |     75
 margarita  | cheese     |    120
 mexicano   | chilli     |     25
 special    | egg        |     25
 garlic     | garlic     |     25
 ham        | ham        |    150
 mushroom   | mushroom   |    100
 napolitana | olives     |     75
 mexicano   | onion      |     75
 vegetarian | peas       |     50
 americano  | pepperoni  |     75
 hawaiian   | pineapple  |    100
 americano  | salami     |    120
 seafood    | seafood    |    200
 mexicano   | spice      |     20
 vegetarian | tomato     |     50

Answer 1

您的两个查询是:

select pizza, ingredient, amount
from recipe
where amount = (select max(amount)
                from recipe r
                where ingredient = r.ingredient
               )
order by ingredient;

和:

select pizza, ingredient, amount
from recipe r
where amount = (select max(amount)
                from recipe
                where ingredient = r.ingredient
                )
order by ingredient;

这两个都被表述为相关子查询。但是，第一个不相关。在条件下:

                where ingredient = r.ingredient

对ingredient 的两个引用都是对内部查询中的表。所以，这基本上是一个空操作。更具体地说，它完全等同于 where r.ingredient is not null。此内部查询返回单个值，即表中 amount 的最大值。

第二个版本是相关的，因此它返回每种成分的最大量。

完全限定所有表名是一个很好的规则。您想要的查询应如下所示:

select r.pizza, r.ingredient, r.amount
from recipe r
where r.amount = (select max(r2.amount)
                  from recipe r2
                  where r2.ingredient = r.ingredient
                 )
order by r.ingredient;