SQL Server 2014 如何通过单查询实现基于客户custRef规则的商品唯一行筛选

当然可以！完全不需要依赖游标遍历处理，我们可以利用SQL Server 2014支持的窗口函数，写出高效的单个查询来满足你的所有筛选规则。

核心思路 #

你的需求本质是对每个客户+商品组合，优先选择与客户custRef匹配的行；如果没有匹配行，则回退选择custRef='100'的行；而当客户自身custRef就是100时，自然只会保留100的行。我们可以用ROW_NUMBER()窗口函数给候选行标记优先级，然后筛选出最优行即可。

完整SQL代码 #

WITH RankedItems AS (
    SELECT 
        c.custNbr,
        i.itemNbr,
        i.custRef,
        i.Name,
        -- 标记优先级：与客户custRef一致的行优先级最高（排第1），100的行排第2
        ROW_NUMBER() OVER (
            PARTITION BY c.custNbr, i.itemNbr
            ORDER BY CASE WHEN i.custRef = c.custRef THEN 1 ELSE 2 END
        ) AS rn
    FROM Customer c
    JOIN Item i ON i.custRef IN (c.custRef, '100')
    -- 如需查询单个客户，添加WHERE条件：WHERE c.custNbr = '0000001'
)
SELECT custNbr, itemNbr, custRef, Name
FROM RankedItems
WHERE rn = 1
ORDER BY custNbr, itemNbr;

代码逻辑拆解 #

1. CTE候选集生成：

   • 关联Customer和Item表，筛选出所有符合条件的候选行：要么商品的custRef与客户的custRef一致，要么商品的custRef是100。
   • 用ROW_NUMBER()按custNbr（客户）和itemNbr（商品）分组，每组内根据优先级排序：和客户custRef匹配的行序号为1，100的行序号为2。

2. 主查询筛选最优行：

   • 只保留每组中序号为1的行，也就是每个客户每个商品的最优匹配结果。

验证示例结果 #

• 查询客户0000001（custRef='100'）：
  所有候选行的custRef都是100，每组的rn都是1，返回结果完全符合预期。
• 查询客户0000002（custRef='120'）：
  • 对于item000001，custRef='120'的行优先级更高（rn=1），被保留；custRef='100'的行被过滤。
  • 对于item000002，只有custRef='100'的行，rn=1被保留，完美匹配预期结果。

优化建议 #

如果数据量较大，建议给以下字段建立索引来提升查询性能：

• Customer(custNbr, custRef)
• Item(custRef, itemNbr)

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