根据筛选客户ID更新Customer表，均匀分配3个销售代表ID

需求背景 #

需要从Customers表中筛选出满足以下条件的客户ID，并将他们均匀分配给指定的3个销售代表：

• 未在指定周期内下单（通过NOT EXISTS关联@TransIds表判断）
• 未分配销售代表（RepId < 1）
• 为企业客户（TypeId > 0且TypeId <> 10）
• 客户状态正常（Inactive = 0、TotalBlock = 0）

要求分配尽可能均匀，避免按ID范围分配导致的客户数量失衡，之前尝试ROW_NUMBER()、变量自增等方法未达预期，最终通过CTE关联查询实现需求。

实现代码 #

WITH 
  Reps AS (SELECT * FROM (VALUES (1), (3), (12)) v(RepId)), -- 指定的销售代表ID
  cte_reps AS (SELECT RepId, ROW_NUMBER() OVER (ORDER BY RepId) - 1 rn, COUNT(*) OVER () cnt FROM Reps),
  cte_customers AS (SELECT *, ROW_NUMBER() OVER (ORDER BY CustomerId) - 1 rn FROM Customers WHERE Customers.CustomerId IN 
  (
        SELECT CustomerId FROM Customers WHERE Inactive = 0 AND TotalBlock = 0 AND RepId < 1 AND TypeId > 0 AND TypeId <> 10 
        AND NOT EXISTS (SELECT CustomerId FROM @TransIds t WHERE t.CustomerId = Customers.CustomerId)
  )
UPDATE c
SET c.RepId = r.RepId
FROM cte_customers c INNER JOIN cte_reps r
ON r.rn = c.rn % r.cnt;

代码说明 #

1. Reps CTE：定义需要分配的销售代表ID列表，可根据实际需求修改括号内的RepId值。
2. cte_reps：为每个销售代表生成从0开始的序号（rn），并计算销售代表总数（cnt）。
3. cte_customers：筛选出符合条件的客户，同时为每个客户生成从0开始的序号（rn）。
4. UPDATE关联：通过客户序号对销售代表总数取模，匹配对应序号的销售代表，实现均匀分配。

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