我想在一个地方列表中选择员工的独特随机发布/招聘地点,所有员工已经在这些地方张贴,我正在尝试为他们生成一个新的随机发布地点,其中“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表进行实际更改.
