mysql - 查询未使用 max 函数带来关联值(Northwinds 数据库)

Question

我正在尝试获取每个类别总值(value)最高的产品。我按类别获得了正确的“总金额”，但似乎没有带来正确的“产品 ID”和“产品名称”。

SELECT 
    c.CategoryName,
    prodGross.ProductID,
    prodGross.ProductName,
    MAX(ROUND(Grossed, 2)) AS Grossed
FROM
    categories AS c
        JOIN
    (SELECT 
        p.ProductID,
            p.ProductName,
            p.CategoryID,
            SUM(((od.UnitPrice * od.Quantity) - ((od.UnitPrice * od.Quantity) * od.Discount))) AS Grossed
    FROM
        northwind.`order details` AS od
    JOIN products AS p ON p.ProductID = od.ProductID
    GROUP BY p.ProductID) AS prodGross ON prodGross.CategoryID = c.CategoryID
GROUP BY c.CategoryName;

任何反馈都会有帮助。谢谢!

Answer 1

考虑与 CategoryID 和 Grossed 字段匹配的两个聚合查询的派生表:

SELECT p.*, c.CategoryName, ROUND(g.MaxGrossed) AS HighestGrossed
FROM category c
INNER JOIN
  (SELECT subp.ProductID, subp.ProductName, subp.CategoryID,
          SUM(((od.UnitPrice * od.Quantity) - 
              ((od.UnitPrice * od.Quantity) * od.Discount))) AS Grossed
   FROM northwind.`order details` AS od
   INNER JOIN products AS subp ON subp.ProductID = od.ProductID
   GROUP BY subp.ProductID) As p
ON c.CategoryID = p.CategoryID

INNER JOIN
   (SELECT subg.CategoryID, MAX(subg.Grossed) AS MaxGrossed
    FROM
      (SELECT subp.ProductID, subp.ProductName, subp.CategoryID,
              SUM(((od.UnitPrice * od.Quantity) - 
                  ((od.UnitPrice * od.Quantity) * od.Discount))) AS Grossed
       FROM northwind.`order details` AS od
       INNER JOIN products AS subp ON subp.ProductID = od.ProductID
       GROUP BY subp.ProductID) AS subg
    GROUP BY subg.CategoryID) AS g
ON p.CategoryID = g.CategoryID AND p.Grossed = g.MaxGrossed

您甚至可以将计算总金额的重复聚合保存为单独的存储 View ，并在此查询中引用它:

SELECT p.*, c.CategoryName, ROUND(g.MaxGrossed) AS HighestGrossed
FROM category c

INNER JOIN ProdGrossedView As p
ON c.CategoryID = p.CategoryID

INNER JOIN
   (SELECT v.CategoryID, MAX(v.Grossed) AS MaxGrossed
    FROM ProdGrossedView v
    GROUP BY v.CategoryID) AS g
ON p.CategoryID = g.CategoryID AND p.Grossed = g.MaxGrossed