随机分配工作地点，每个地点不得超过指定员工人数 [英] Randomly assign work location and each location should not exceed the number of designated employees

问题描述

我正在尝试在位置列表中选择员工的唯一随机发布/招聘位置，所有员工都已在这些位置发布，我正在尝试为他们生成一个新的随机发布位置，并带有where"条件员工新的随机位置将不等于他们的家乡，随机选择的员工的指定必须小于或等于 Places 表中的 Place wise 指定编号"

I am trying to select unique random posting/recruitment places of employees within a list of places, all the employees are already posted at these places, i am trying to generate a new random posting place for them with "where" condition that "employee new random location will not be equal to their home place and randomnly selected Employees with their designation must be less than or equal to Place wise designation numbers from Places table "

员工表是:

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 与员工人数 - 指定明智)是:-

And the Places table (PlaceNames with number of employees - designation wise) is :-

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 地名的员工，

tried with with newid() like as below and to be able to select Employees with RandomPosting place names,

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(在员工中)和 Place明智的指定不会超过给定的数字.

additionally I need to limit the random selection based upon designation numbers(in Places table)... that is to assign each Employee a PlaceName(from Places) randomly which is not equal to CurrentPosting and Home(in Employee) and Place wise designation will not exceed as given numbers.

提前致谢.

推荐答案

也许是这样的:

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

That should attempt to match employees randomly based on their designation discarding same currentPosting and home, and not assign more than what is specified in each column for the designation. However, this could return the same employee for several places, since they could match more than one based on that criteria.

在看到你关于不需要高性能单一查询来解决这个问题的评论(我不确定这是否可能)之后，并且因为它似乎更像是一个一次性"过程，你将调用，我用一个游标和一个临时表写了下面的代码来解决你的赋值问题:

After seeing your comment about not having a need for a high performing single query to solve this problem (which I'm not sure is even possible), and since it seems to be more of a "one-off" process that you will be calling, I wrote up the following code using a cursor and one temporary table to solve your problem of assignments:

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

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

The basic idea is, that it iterates over the employees, in random order, and assigns to each one a random Place that meets the criteria of different home and current posting, as well as controlling the amount that get assigned to each place for each Designation to ensure that the locations are not "over-assigned" for each role.

这个片段实际上并没有改变您的数据.最后的 SELECT 语句只返回建议的分配.但是，您可以非常轻松地对其进行更改，以相应地对 Employee 表进行实际更改.

This snippet doesn't actually alter your data though. The final SELECT statement just returns the proposed assignments. However you could very easily alter it to make actual changes to your Employee table accordingly.

这篇关于随机分配工作地点，每个地点不得超过指定员工人数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！