如何按配送方式找出订单量最高的供应商？子查询使用困惑

你写的SQL问题出在这两处 #

1. 内层子查询的GROUP BY引用了外部查询的OI.SHIPPINGMETHOD、L.SUPPLIERNAME，这属于非法引用，得改成自己的OI2.SHIPPINGMETHOD、L2.SUPPLIERNAME。
2. 最外层的子查询没和当前分组的shippingmethod关联，算出来的是所有配送方式里的全局最大订单量，所以最后只返回了订单量等于这个全局最大值的那一行，而不是每个配送方式下的第一名。

正确的实现方法 #

方法一：用窗口函数（推荐，简洁高效） #

窗口函数可以直接按配送方式分组，给每个供应商的订单量排个序，然后挑出每组的第一名：

SELECT shippingmethod, suppliername, amount
FROM (
    SELECT 
        oi.shippingmethod,
        s.name AS suppliername,
        COUNT(oi.order_id) AS amount,
        ROW_NUMBER() OVER (PARTITION BY oi.shippingmethod ORDER BY COUNT(oi.order_id) DESC) AS rn
    FROM orderitem oi
    JOIN supplier s ON s.supplier_id = oi.supplier
    GROUP BY oi.shippingmethod, s.supplier_id, s.name
) t
WHERE rn = 1;

简单解释：

• PARTITION BY oi.shippingmethod：把数据按配送方式分成一个个小组
• ORDER BY COUNT(oi.order_id) DESC：每个小组里，按订单量从多到少排序
• ROW_NUMBER()：给每个小组里的记录编序号，订单最多的是1，最后只留序号为1的记录就行

方法二：用关联子查询（兼容老版本数据库） #

先统计每个配送方式+供应商的订单量，再算出每个配送方式的最大订单量，最后把这俩结果关联起来，找到对应供应商：

-- 先统计每个配送方式下各供应商的订单量
WITH supplier_order_counts AS (
    SELECT 
        oi.shippingmethod,
        s.name AS suppliername,
        COUNT(oi.order_id) AS amount
    FROM orderitem oi
    JOIN supplier s ON s.supplier_id = oi.supplier
    GROUP BY oi.shippingmethod, s.supplier_id, s.name
),
-- 再统计每个配送方式的最大订单量
max_per_shipping AS (
    SELECT shippingmethod, MAX(amount) AS max_amount
    FROM supplier_order_counts
    GROUP BY shippingmethod
)
-- 最后关联两个结果，找出每个配送方式下订单量等于最大值的供应商
SELECT 
    soc.shippingmethod,
    soc.suppliername,
    soc.amount
FROM supplier_order_counts soc
JOIN max_per_shipping mps 
    ON soc.shippingmethod = mps.shippingmethod 
    AND soc.amount = mps.max_amount;

简单解释：

• 第一个CTE就是你最初写的统计查询，先拿到所有配送方式+供应商的订单数
• 第二个CTE算出每个配送方式的最高订单量
• 最后把这两个结果关联，筛选出每个配送方式里订单量等于最高值的记录

说说子查询的使用场景 #

给你总结几个适合用子查询的情况：

• 需要先算出一个中间结果，再基于这个结果继续筛选（比如上面先算最大订单量，再找对应供应商）
• 要在WHERE或HAVING里用聚合结果的时候（比如判断某条记录的数值是不是分组里的最大值）
• 查询逻辑需要分层处理，先拿子集再处理全集的时候

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