SQL Server按日期和商品分组求最大最小采购量及对应采购方

针对你的需求，我们可以利用窗口函数高效实现按DATE和ITEM分组，提取每组采购量最高、最低的采购方及对应数量。以下是两种适用于不同场景的解决方案：

场景1：每组最高/最低采购量唯一（或只需取任意一个） #

如果每组的最高/最低采购量对应的采购方只有一个，或者你只需要返回其中一个，可使用ROW_NUMBER()窗口函数：

WITH RankedData AS (
    SELECT 
        DATE,
        ITEM,
        BUYER,
        QTY_BUY,
        -- 按采购量降序排名，排名1即为该组最高采购量记录
        ROW_NUMBER() OVER (PARTITION BY DATE, ITEM ORDER BY QTY_BUY DESC) AS rn_top,
        -- 按采购量升序排名，排名1即为该组最低采购量记录
        ROW_NUMBER() OVER (PARTITION BY DATE, ITEM ORDER BY QTY_BUY ASC) AS rn_min
    FROM PurchaseData -- 替换为你的实际表名
)
SELECT 
    t.DATE,
    t.ITEM,
    t.BUYER AS TOP_BUYER,
    t.QTY_BUY AS TOP_QTY,
    m.BUYER AS MIN_BUYER,
    m.QTY_BUY AS QTY_MIN
FROM RankedData t
INNER JOIN RankedData m 
    ON t.DATE = m.DATE 
    AND t.ITEM = m.ITEM 
    AND t.rn_top = 1 
    AND m.rn_min = 1
GROUP BY t.DATE, t.ITEM, t.BUYER, t.QTY_BUY, m.BUYER, m.QTY_BUY;

场景2：存在多个采购方拥有相同最高/最低采购量 #

如果同一组内有多个采购方采购量相同且为最高/最低，可使用DENSE_RANK()结合STRING_AGG()将所有对应采购方合并展示：

WITH RankedData AS (
    SELECT 
        DATE,
        ITEM,
        BUYER,
        QTY_BUY,
        DENSE_RANK() OVER (PARTITION BY DATE, ITEM ORDER BY QTY_BUY DESC) AS rn_top,
        DENSE_RANK() OVER (PARTITION BY DATE, ITEM ORDER BY QTY_BUY ASC) AS rn_min
    FROM PurchaseData -- 替换为你的实际表名
)
SELECT 
    DATE,
    ITEM,
    STRING_AGG(CASE WHEN rn_top = 1 THEN BUYER END, ', ') AS TOP_BUYER,
    MAX(CASE WHEN rn_top = 1 THEN QTY_BUY END) AS TOP_QTY,
    STRING_AGG(CASE WHEN rn_min = 1 THEN BUYER END, ', ') AS MIN_BUYER,
    MIN(CASE WHEN rn_min = 1 THEN QTY_BUY END) AS QTY_MIN
FROM RankedData
GROUP BY DATE, ITEM;

关键说明 #

• PARTITION BY DATE, ITEM：指定分组依据，确保排名仅在同一日期、同一商品组内进行。
• ROW_NUMBER()：给每条记录分配唯一排名，若存在相同采购量，排名会随机分配（适合只需要单个结果的场景）。
• DENSE_RANK()：相同采购量会获得相同排名，配合STRING_AGG()可将所有并列的采购方用逗号分隔合并显示。

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