本文介绍了循环分配的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个客户表,并希望以循环方式为每个客户分配一个销售人员.
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
这篇关于循环分配的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!