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

我想在一个地方列表中选择员工的独特随机发布/招聘地点,所有员工已经在这些地方张贴,我正在尝试为他们生成一个新的随机发布地点,其中“where”条件为“员工新随机位置将不等于其所在地并随机选择具有其名称的员工必须小于或等于“地方”表中的“地方明智”指定号码“

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

（编辑：安卓应用网）

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