循环作业 [英] Round-robin assignment

问题描述

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

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.

任何想法都将不胜感激！

Any ideas are greatly appreciated!

推荐答案

在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屋！