循环分配 [英] Round-robin assignment

问题描述

我有一个客户表，并希望以循环方式为每个客户分配一个销售人员.

I have a Customers table and would like to assign a Salesperson to each customer in a round-robin fashion.

Customers  
--CustomerID  
--FName  
--SalespersonID

Salesperson  
--SalespersonID  
--FName  

所以，如果我有 15 位客户和 5 位销售人员，我希望最终结果看起来像这样:

So, if I have 15 customers and 5 salespeople, I would like the end result to look something like this:

CustomerID -- FName -- SalespersonID  
1 -- A -- 1  
2 -- B -- 2  
3 -- C -- 3  
4 -- D -- 4  
5 -- E -- 5  
6 -- F -- 1  
7 -- G -- 2  
8 -- H -- 3  
9 -- I -- 4  
10 -- J -- 5  
11 -- K -- 1  
12 -- L -- 2  
13 -- M -- 3  
14 -- N -- 4  
15 -- 0 -- 5  

等等……

我一直在玩这个，正在尝试编写一些 SQL 来使用适当的 SalespersonID 更新我的客户表，但是在让它工作时遇到了一些麻烦.

I've been playing around with this for a bit and am trying to write some SQL to update my Customers table with the appropriate SalespersonID, but am having some trouble getting it to work.

非常感谢任何想法！

推荐答案

在SQL Server中:

WITH    с AS
        (
        SELECT  *, ROW_NUMBER() OVER ORDER BY (customerID) AS rn
        FROM    customers
        ),
        s AS
        SELECT  *,
                ROW_NUMBER() OVER ORDER BY (SalespersonID) AS rn
        FROM    salesPersons
        )
SELECT  c.*, s.*
FROM    с
JOIN    s
ON      s.rn =
        (с.rn - 1) %
        (
        SELECT  COUNT(*)
        FROM    salesPersons
        ) + 1

这篇关于循环分配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！