随机分配工作场所，每个场所不得超过指定员工人数

Question

我试图在一个位置列表中选择员工唯一的随机发布/招聘位置，所有员工都已经发布在这些位置上，我试图为他们生成一个新的随机发布位置，条件是“员工新的随机位置将不等于他们的家庭位置，随机选择的员工必须小于或等于places表中的位置智能指定数字”

Employee表是：

EmpNo   EmpName           CurrentPosting    Home        Designation   RandomPosting
1       Mac               Alabama           Missouri      Manager       
2       Peter             California        Montana       Manager       
3       Prasad            Delaware          Nebraska      PO       
4       Kumar             Indiana           Nevada        PO       
5       Roy               Iowa              New Jersey    Clerk       

以此类推。

而Places表(带有雇员数量的PlaceNames)是:-

PlaceID  PlaceName      Manager     PO    Clerk
1        Alabama           2        0     1
2        Alaska            1        1     1
3        Arizona           1        0     2
4        Arkansas          2        1     1
5        California        1        1     1
6        Colorado          1        1     2
7        Connecticut       0        2     0

以此类推。

尝试使用如下所示的newid()，并能够选择具有RandomPosting地名的员工，

WITH cteCrossJoin AS (
SELECT e.*, p.PlaceName AS RandomPosting,
       ROW_NUMBER() OVER(PARTITION BY e.EmpNo ORDER BY NEWID()) AS RowNum
    FROM Employee e
        CROSS JOIN  Place p
    WHERE e.Home <> p.PlaceName
)
SELECT *
FROM cteCrossJoin
WHERE RowNum = 1;

此外，我需要限制随机选择的基础上指定编号(在位置表)…也就是说，随机地为每个员工分配一个位置(从位置)，它不等于CurrentPosting和PlaceName(在Employee中)，并且位置智能指定不会超过给定的数字。

提前谢谢。

Answer 1

可能是这样的：

select C.* from 
(
    select *, ROW_NUMBER() OVER(PARTITION BY P.PlaceID, E.Designation ORDER BY NEWID()) AS RandPosition
        from Place as P cross join Employee E
    where P.PlaceName != E.Home AND P.PlaceName != E.CurrentPosting
) as C
where 
    (C.Designation = 'Manager' AND C.RandPosition <= C.Manager) OR
    (C.Designation = 'PO' AND C.RandPosition <= C.PO) OR
    (C.Designation = 'Clerk' AND C.RandPosition <= C.Clerk)

这应该尝试根据员工的指定随机匹配员工，丢弃相同的currentPosting和home，并且分配的值不会超过每个列中为指定指定的值。但是，这可能会返回多个位置的同一员工，因为他们可以基于该标准匹配多个位置。

EDIT:在看到您关于不需要高性能的单个查询来解决此问题(我甚至不确定是否可能)的评论后，由于您将调用它似乎更像是一个“一次性”过程，因此我使用游标和一个临时表编写了以下代码来解决您的赋值问题：

select *, null NewPlaceID into #Employee from Employee

declare @empNo int
DECLARE emp_cursor CURSOR FOR  
SELECT EmpNo from Employee order by newid()

OPEN emp_cursor   
FETCH NEXT FROM emp_cursor INTO @empNo

WHILE @@FETCH_STATUS = 0   
BEGIN
    update #Employee 
    set NewPlaceID = 
        (
        select top 1 p.PlaceID from Place p 
        where 
            p.PlaceName != #Employee.Home AND 
            p.PlaceName != #Employee.CurrentPosting AND
            (
                CASE #Employee.Designation 
                WHEN 'Manager' THEN p.Manager
                WHEN 'PO' THEN p.PO
                WHEN 'Clerk' THEN p.Clerk
                END
            ) > (select count(*) from #Employee e2 where e2.NewPlaceID = p.PlaceID AND e2.Designation = #Employee.Designation)
        order by newid()
        ) 
    where #Employee.EmpNo = @empNo
    FETCH NEXT FROM emp_cursor INTO @empNo   
END

CLOSE emp_cursor
DEALLOCATE emp_cursor

select e.*, p.PlaceName as RandomPosting from Employee e
inner join #Employee e2 on (e.EmpNo = e2.EmpNo)
inner join Place p on (e2.NewPlaceID = p.PlaceID)

drop table #Employee

基本思想是，它以随机顺序迭代员工，并为每个员工分配一个满足不同家庭和当前发布标准的随机地点，以及控制为每个指定分配到每个地点的数量，以确保不会为每个角色“过度分配”这些地点。

不过，这个代码片段实际上并不会更改您的数据。最后的SELECT语句只返回建议的赋值。但是，您可以很容易地对它进行更改，从而相应地对Employee表进行实际更改。

Answer 2

我假设约束是：

• 员工不能转到他/她当前所在的同一地点。
• 所有站点在每个类别中必须至少有一名员工。

最重要的想法是认识到你并不是在寻找一个“随机”的任务。您正在寻找位置的排列，这取决于每个人都移动到其他地方的条件。

我将为管理者描述一个答案。对于每种类型的员工，您可能需要三个查询。

关键的想法是一个ManagerPositions表。这有一个位置、一个序列号和一个在该位置内的序列号。下面是一个示例：

Araria     1    1
Araria     2    2
Arwal      1    3
Arungabad  1    4

该查询通过使用row_number()函数连接到INFORMATION_SCHEMA.columns来分配序列，从而创建该表。这是在SQL Server中获取序列的一种快速而粗糙的方法--但只要所需的最大数目(即任何位置的管理器的最大数目)小于数据库中的列数，这种方法就完全有效。还有其他方法可以处理更一般的情况。

下一个关键的想法是旋转位置，而不是随机选择它们。这使用了模算术的思想--加上一个偏移量，然后取位置总数的余数。最后的查询如下所示：

with ManagerPositions as (
     select p.*,
            row_number() over (order by placerand, posseqnum) as seqnum,
            nums.posseqnum
     from (select p.*, newid() as placerand
           from places p
          ) p join
          (select row_number() over (order by (select NULL)) as posseqnum
           from INFORMATION_SCHEMA.COLUMNS c
          ) nums
          on p.Manager <= nums.posseqnum
    ),
   managers as (
    select e.*, mp.seqnum
    from (select e.*,
                 row_number() over (partition by currentposting order by newid()
                                   ) as posseqnum
          from Employees e              
          where e.Designation = 'Manager'
         ) e join
         ManagerPositions mp
         on e.CurrentPosting = mp.PlaceName and
            e.posseqnum = mp.posseqnum
  )
select m.*, mp.PlaceId, mp.PlaceName
from managers m cross join
     (select max(seqnum) as maxseqnum, max(posseqnum) as maxposseqnum
      from managerPositions mp
     ) const join
     managerPositions mp
     on (m.seqnum+maxposseqnum+1) % maxseqnum + 1 = mp.seqnum

好吧，我知道这很复杂。每个经理职位都有一个表(而不是语句中的计数，每个职位占一行很重要)。有两种方法可以识别一个位置。第一个是按位置和按位置内的计数(posseqnum)。第二种是通过行上的递增id。

在表格中查找每个经理的当前职位。这应该是唯一的，因为我考虑了每个地方的经理数量。然后，向该位置添加偏移量，并指定该位置。通过使偏移量大于maxseqnum，可以保证管理器移动到另一个位置(除非在一个位置有超过一半的管理器的不寻常边界情况)。

如果所有当前经理职位都已填满，则可以保证所有职位都将转移到下一个位置。因为ManagerPositions使用随机id来分配seqnum，所以“下一个”位置是随机的，而不是按id或字母顺序排列的。

此解决方案确实让许多员工一起前往相同的新地点。可以通过在表达式编辑器( expression (m.seqnum+maxposseqnum+1) )中尝试"1“以外的值来在一定程度上修复此问题。

我意识到有一种方法可以修改这一点，以防止当前位置和下一个位置之间的关联。这将执行以下操作：

1. 将序号分配给ManagerPosition表中的不同偏移量，并根据表中由该偏移量分隔的两个位置相同的次数对每个偏移量进行评级。
2. 选择具有最小评级的偏移量(最好是0)。
3. 在最终的匹配子句中使用该偏移量。

我现在没有足够的时间来编写SQL。