SQL求各供应商销量最高产品问题求助（MAX/SUM函数异常）

问题背景 #

需要查询每个供应商销量最高的产品，涉及4张表：

• Supplier（ID、SName）：供应商表，存储供应商ID和名称
• Order（ID、Supplier_ID）：订单表，存储订单ID和对应的供应商ID
• Order_Position（ID、Order_ID、Product_ID、quantity）：订单明细表，存储订单对应的产品ID和购买数量
• Products（ID、PName）：产品表，存储产品ID和名称

用户尝试编写的SQL代码（存在逻辑和语法错误）：

SELECT X.ID, X.SNAME
FROM (SELECT S.ID, P.PNAME, SUM(OP.QUANTITY) AS SUM
      FROM ORDER_POSITION AS OP
      INNER JOIN ORDER AS O ON O.ID = OP.ORDER_ID
      INNER JOIN SUPPLIER AS S ON S.ID = O.SUPPLIER_ID
      INNER JOIN PRODUCT AS P ON P.ID = OP.PRODUCT_ID
      GROUP BY S.ID, P.PNAME) AS X
WHERE X.SUM = (SELECT (MAX(OP.QUANTITY) AS MAXSUM
                       FROM ORDER_POSITION AS OP)

原代码问题分析 #

1. 逻辑错误：子查询MAX(OP.QUANTITY)取的是所有订单明细中单条记录的最大数量，而非每个供应商下产品的总销量最大值，无法匹配每个供应商的最高销量产品。
2. 语法错误：子查询中括号使用错误，(MAX(OP.QUANTITY) AS MAXSUM多了一个左括号，且整个子查询未闭合。
3. 结果缺失：最终只返回了供应商ID和名称，未包含对应的销量最高产品信息，不符合需求。

解决方案 #

方法1：使用窗口函数（推荐，适用于支持窗口函数的数据库：MySQL 8+、PostgreSQL、SQL Server等） #

窗口函数可以轻松实现“分组取Top N”的需求，代码清晰高效：

SELECT 
    供应商ID,
    供应商名称,
    销量最高产品,
    总销量
FROM (
    SELECT 
        s.ID AS 供应商ID,
        s.SName AS 供应商名称,
        p.PName AS 销量最高产品,
        SUM(op.quantity) AS 总销量,
        -- 按供应商分组，产品总销量降序排名
        RANK() OVER(PARTITION BY s.ID ORDER BY SUM(op.quantity) DESC) AS 销量排名
    FROM Order_Position op
    -- 注意Order是关键字，需用反引号/方括号包裹
    JOIN `Order` o ON o.ID = op.Order_ID
    JOIN Supplier s ON s.ID = o.Supplier_ID
    JOIN Products p ON p.ID = op.Product_ID
    GROUP BY s.ID, s.SName, p.ID, p.PName
) AS 供应商产品销量统计
WHERE 销量排名 = 1;

• 说明：RANK()函数会给同一供应商下销量并列第一的产品都标记为排名1；如果只想取其中一个（比如按产品ID排序取第一个），可以替换为ROW_NUMBER()。

方法2：使用关联子查询（兼容旧版数据库） #

如果数据库不支持窗口函数，可以用嵌套子查询实现：

SELECT 
    s.ID AS 供应商ID,
    s.SName AS 供应商名称,
    p.PName AS 销量最高产品,
    SUM(op.quantity) AS 总销量
FROM Order_Position op
JOIN `Order` o ON o.ID = op.Order_ID
JOIN Supplier s ON s.ID = o.Supplier_ID
JOIN Products p ON p.ID = op.Product_ID
GROUP BY s.ID, s.SName, p.ID, p.PName
HAVING SUM(op.quantity) = (
    -- 子查询获取当前供应商下所有产品的最高总销量
    SELECT MAX(产品总销量)
    FROM (
        SELECT SUM(op2.quantity) AS 产品总销量
        FROM Order_Position op2
        JOIN `Order` o2 ON o2.ID = op2.Order_ID
        WHERE o2.Supplier_ID = s.ID
        GROUP BY op2.Product_ID
    ) AS 供应商产品销量
);

• 说明：内层子查询先计算当前供应商下每个产品的总销量，外层取该供应商的最大销量值，最后筛选出总销量等于该最大值的产品。

内容的提问来源于stack exchange，提问作者forg