sql – 随机分配工作地点,每个地点不应超过指定员工的数量

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;

另外我需要根据指定号码限制随机选择(在Places表格中)……即为每个员工随机分配一个PlaceName(来自Places),这不等于CurrentPosting和Home(在Employee中)和Place wise指定不超过给定的数字.

提前致谢.

解决方法

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,而不是指定超过每列中指定的名称.但是,这可能会在多个地方返回同一名员工,因为他们可以根据该条件匹配多个员工.

编辑：
看到你的评论没有需要一个高性能的单一查询来解决这个问题(我甚至不确定是否可能),并且因为它似乎更像是一个“一次性”的过程,你将成为在调用时,我使用游标和一个临时表编写了以下代码来解决您的分配问题：

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表进行实际更改.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!