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

在有人问之前，是的，这是家庭作业的一部分，但是，我尝试并寻找了类似的解决方案，但没有一个起作用。

还要注意的重要一点是，它应该在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将会从内部子查询中返回相同的元组。

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

尚不清楚的地方，或者您需要有关表及其关系的更多信息，请告诉我。

最佳答案

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订购啤酒。

关于mysql - 如何从子查询中选择具有MAX值的整个元组？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37639435/