经典模型中查询各产品线最高订购量客户的SQL问题

原查询的问题 #

原查询依赖子查询的排序结果，外层按productline分组时，数据库只会返回每组的第一条记录，当同一产品线存在多个订购量相同的最高客户时，其他并列客户会被丢弃，无法满足需求。

方案一：使用窗口函数（推荐） #

利用RANK()或DENSE_RANK()窗口函数，可以直接标记出各产品线中订购量排名第一的客户，包括并列的情况。RANK()会跳过并列后的排名，DENSE_RANK()不会，可根据需求选择。

WITH customer_product_totals AS (
    SELECT 
        pl.productline,
        c.customerName,
        SUM(od.quantityOrdered) AS totale
    FROM customers c
    JOIN orders o ON c.customerNumber = o.customerNumber
    JOIN orderdetails od ON o.orderNumber = od.orderNumber
    JOIN products p ON od.productCode = p.productCode
    JOIN productlines pl ON p.productLine = pl.productLine
    GROUP BY pl.productline, c.customerName
),
ranked_customers AS (
    SELECT 
        productline,
        customerName,
        totale,
        RANK() OVER (PARTITION BY productline ORDER BY totale DESC) AS rnk
    FROM customer_product_totals
)
SELECT productline, customerName, totale
FROM ranked_customers
WHERE rnk = 1;

说明 #

1. 第一个CTEcustomer_product_totals先计算每个客户在各产品线的总订购量，用显式JOIN替代原查询的隐式JOIN，可读性更强。
2. 第二个CTEranked_customers用RANK()窗口函数，按产品线分组（PARTITION BY productline），按总订购量降序排序，给每个客户标记排名。
3. 最后筛选出排名为1的记录，即可得到各产品线订购量最高的所有客户（含并列）。

如果需要连续排名（比如并列第一都算1，下一个是2），可以把RANK()换成DENSE_RANK()，效果类似，仅排名计数逻辑不同。

方案二：使用关联子查询 #

如果你的数据库不支持窗口函数（如旧版MySQL），可以用关联子查询匹配各产品线的最高订购量：

SELECT 
    pl.productline,
    c.customerName,
    SUM(od.quantityOrdered) AS totale
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
JOIN orderdetails od ON o.orderNumber = od.orderNumber
JOIN products p ON od.productCode = p.productCode
JOIN productlines pl ON p.productLine = pl.productLine
GROUP BY pl.productline, c.customerName
HAVING SUM(od.quantityOrdered) = (
    SELECT MAX(t.totale)
    FROM (
        SELECT SUM(od2.quantityOrdered) AS totale
        FROM orders o2
        JOIN orderdetails od2 ON o2.orderNumber = od2.orderNumber
        JOIN products p2 ON od2.productCode = p2.productCode
        JOIN productlines pl2 ON p2.productLine = pl2.productLine
        WHERE pl2.productline = pl.productline
        GROUP BY o2.customerNumber
    ) t
);

说明 #

1. 外层查询先计算每个客户的产品线总订购量。
2. 关联子查询针对当前产品线（pl2.productline = pl.productline），计算该产品线所有客户的最高订购量。
3. 用HAVING子句筛选出总订购量等于该产品线最高值的客户，从而保留并列的情况。

你之前尝试WHERE totale=(SELECT...)报错，大概率是因为子查询没有关联当前产品线，导致返回全局最高订购量而非对应产品线的最高值；或是因为totale是聚合函数别名，不能直接在WHERE中使用（需用HAVING或子查询）。

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