sql - 在地点列表中选择员工唯一的随机发布/招聘地点

Question

我正在尝试在地点列表中选择员工的唯一随机发布/招聘地点，所有员工都已经在这些地点发布，我正在尝试为他们生成一个新的随机发布地点，条件是“where” “员工新的随机位置将不等于他们的家”员工表是:

EmpNo   Empname           CurrentPosting    Home        Designation RandomPosting
1       Satish Kumar      Samastipur        Gazi        Manager     
2       Anil Kumar Singh  Vaishali          Patna       Manager     
3       Rajdev Prasad     Nawada            Gaya        PO      
4       Rajesh Kumar      Sheikhpura        Muzaffarpur PO      
5       Jitendra Kumar    Banka             Bhagalpur   Clerk       

等等……

Places 表是

PlaceID  PlaceName      Manager     PO    Clerk
1        Araria            2        0     1
2        Arwal             1        1     1
3        Aurangabad        1        0     2
4        Banka             2        1     1
5        Begusarai         1        1     1
6        Bhagalpur         1        1     2
7        Bhojpur           0        2     0

等等……

我尝试使用 rand() 和 newid() 如下所示，

select Employee.*, Place.PlaceName As RandomPosting  from Employee 
inner join Place on Place.PlaceID=Employee.EmpNo order by newid()

但无法选择所需的...即为每个员工随机分配一个不等于 CurrentPosting 和 Home(in Employee) 的 PlaceName(from Place)。

提前致谢。

Answer 1

WITH cteCrossJoin AS (
    SELECT e.*, p.PlaceName,
           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;