SQL Server中按ItemNo筛选对应最高SumDemand的StoreNo

核心需求回顾 #

• 每个ItemNo仅显示SumDemand最高的StoreNo（若多个门店需求相同且为最大值，保留所有这些记录）
• 排除所有门店SumDemand均为0的ItemNo

实现代码 #

方案一：简洁版（窗口函数一步到位） #

WITH ItemStoreDemand AS (
    -- 替换为你的分组求和查询，此处是原表分组求和示例
    SELECT 
        ItemNo, 
        StoreNo, 
        SUM(DemandYr1 + DemandYr2 + DemandYr3) AS SumDemand
    FROM YourOriginalTable
    GROUP BY ItemNo, StoreNo
)
SELECT ItemNo, StoreNo, SumDemand
FROM (
    SELECT 
        ItemNo,
        StoreNo,
        SumDemand,
        -- 按商品分组，需求降序排名，相同最大值的门店共享同一排名
        RANK() OVER (PARTITION BY ItemNo ORDER BY SumDemand DESC) AS DemandRank,
        -- 计算每个商品的最高需求值，用于排除全0商品
        MAX(SumDemand) OVER (PARTITION BY ItemNo) AS MaxSumPerItem
    FROM ItemStoreDemand
) t
WHERE DemandRank = 1 -- 筛选最高需求的门店
  AND MaxSumPerItem > 0; -- 排除所有门店需求均为0的商品

方案二：分步CTE版（逻辑更直观） #

-- 步骤1：生成商品-门店的需求总和中间结果
WITH ItemStoreDemand AS (
    SELECT 
        ItemNo, 
        StoreNo, 
        SUM(DemandYr1 + DemandYr2 + DemandYr3) AS SumDemand
    FROM YourOriginalTable
    GROUP BY ItemNo, StoreNo
),
-- 步骤2：为每个商品下的门店按需求排序排名
ItemMaxDemand AS (
    SELECT 
        ItemNo,
        StoreNo,
        SumDemand,
        RANK() OVER (PARTITION BY ItemNo ORDER BY SumDemand DESC) AS DemandRank
    FROM ItemStoreDemand
),
-- 步骤3：筛选出至少有一个门店需求大于0的商品
ItemValidItems AS (
    SELECT ItemNo
    FROM ItemStoreDemand
    GROUP BY ItemNo
    HAVING MAX(SumDemand) > 0
)
-- 最终结果：关联有效商品与对应最高需求门店
SELECT imd.ItemNo, imd.StoreNo, imd.SumDemand
FROM ItemMaxDemand imd
JOIN ItemValidItems ivi ON imd.ItemNo = ivi.ItemNo
WHERE imd.DemandRank = 1;

关键逻辑说明 #

• 选用RANK()而非ROW_NUMBER()：RANK()会为相同SumDemand的门店分配相同排名，满足“允许同最大值重复记录”的要求；ROW_NUMBER()会强制给相同值分配不同排名，不符合需求。
• 排除全0商品：通过计算每个商品的最大需求值是否大于0，确保只保留存在有效需求的商品。

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