mysql - 如何从子查询中选择具有 MAX 值的整个元组？

Question

在有人问之前，是的，这是作业的一部分，但是，我已经尝试并搜索过类似的解决方案，但都没有奏效。

同样需要注意的是，它应该在 MySQL 服务器上运行，因此应该考虑它们的任何特殊语法。

为此，您需要四个表:

person (idperson INT, firstName VARCHAR(45))
beer (idbeer INT, name VARCHAR(45))
purchase(idpurchase INT, idperson INT)
beerxpurchase(idpurchase INT, idbeer INT, quantity INT)

除了 beerxpurchase 外，第一列中的每一列都是主键，它是来自第一列和第二列的组合键，所有其他共享名称的列代表一个外键。

最后，我需要做一个查询，返回每个人在他们所有购买中购买最多的啤酒的 ID，以及他们购买的那种啤酒的总量。

如下查询:

SELECT person.firstName NAME, beerxpurchase.idBeer BEER, SUM(beerxpurchase.quantity) TOTAL
FROM person

INNER JOIN purchase
on person.idperson = purchase.idperson

INNER JOIN beerxpurchase
ON beerxpurchase.idpurchase = purchase.idpurchase

GROUP BY person.firstName, beerxpurchase.idbeer;

将返回每个人为每个特定啤酒代码购买的啤酒总量，如下所示:

NAME   BEER    TOTAL
A      1       5
A      2       23
A      3       3
A      4       19
A      5       7
B      1       11
B      2       3
B      3       3
B      4       4
B      5       5
C      3       5
D      1       8
D      2       4
D      4       1
D      5       10
E      1       9
E      4       10

我认为这是一个好的开始，尽管如此，从该查询中我应该只获得每个用户拥有最大数量的元组。听起来像 GROUP BY NAME，但是，运行如下:

SELECT preferenence.FIRST_NAME, preferenence.ID_BEER, MAX(preferenence.TOTAL_BOUGHT) FROM
(
    SELECT person.firstName FIRST_NAME, beerxpurchase.idBeer ID_BEER, SUM(beerxpurchase.quantity) TOTAL_BOUGHT
    FROM person

    INNER JOIN purchase
    on person.idperson = purchase.idperson

    INNER JOIN beerxpurchase
    ON beerxpurchase.idpurchase = purchase.idpurchase

    GROUP BY person.firstName, beerxpurchase.idbeer
) preferenence

GROUP BY preferenence.FIRST_NAME
ORDER BY MAX(preferenence.TOTAL_BOUGHT) DESC;

会丢失正确的啤酒 ID，但会保留正确的人名和购买的啤酒总量，如下所示:

FIRST_NAME     ID_BEER      TOTAL_BOUGHT
A              1            23
B              1            11
D              1            10
E              1            10
C              3            5

据我所知，大多数 DBMS 不会跟踪 ID_BEER，因为它不是 GROUP 的一部分(据我所知)。但是，将最后一个 GROUP 更改为 GROUP BY preference.FIRST_NAME, preference.ID_BEER 将从内部子查询返回相同的元组。

所以问题是:如何从该子查询继续获取与该特定最大值关联的整个元组？或者，如果您可以为子查询或一般查询提出更好的解决方案，我们非常欢迎。

任何不完全清楚的地方，或者如果您需要有关表格及其关系的更多信息，请告诉我。

Answer 1

SELECT p.firstName
     , b.name beer
     , m.total
  FROM 
     ( SELECT o.idperson
            , bo.idbeer
            , SUM(bo.quantity) total
         FROM beerxpurchase bo 
         JOIN purchase o 
           ON o.idpurchase = bo.idpurchase 
        GROUP
           BY o.idperson
            , bo.idbeer
     ) m
  JOIN 
     ( SELECT idperson
            , MAX(total) total
         FROM 
            ( SELECT o.idperson
                   , bo.idbeer
                   , SUM(bo.quantity) total
                FROM beerxpurchase bo 
                JOIN purchase o 
                  ON o.idpurchase = bo.idpurchase 
               GROUP
                  BY o.idperson
                   , bo.idbeer
            ) x
        GROUP
           BY idperson
     ) n
    ON n.idperson = m.idperson
   AND n.total = m.total
  JOIN person p
    ON p.idperson = m.idperson
  JOIN beer b
    ON b.idbeer = m.idbeer

http://sqlfiddle.com/#!9/c6bb9/15

我知道您没有询问啤酒的名称，但我个人倾向于按名称而不是 ID 订购啤酒。