sql-server - 如何在 TSQL 中将一个表中的行均匀分布到另一个表中的行？

Question

我正在尝试找出一个 SQL 查询，它将一个表中的记录均匀分布或分配到另一个表。最好的解释方法是通过一个人为的例子。

假设我有一张员工表，我想从颜色表中为每个员工分配一种颜色。

我想确保颜色均匀分布，但没有任何员工属性可以预测他们会收到哪种颜色。

员工:

Sam
John
Jack
April
Sonny
Jill
Jane

颜色:

Red
Green
Blue

结果:

Sam - Red
John - Green
Jack - Blue
April - Red
Sonny - Green
Jill - Blue
Jane - Red

如何在 TSQL 中构造此连接？

Answer 1

我知道这个问题是关于 SQLServer 的，但是对于那些对没有 NTILE 而只使用 row_number 的解决方案感兴趣的人:

-- (PostgreSQL syntax, but can be easly adapted to any DB)

with 

-- "uses" a dummy colors table just for testing
colors as 
  (select 'Red' as color union all
   select 'Green'        union all
   select 'Blue' 
  )
,

-- "uses" a dummy employees table just for testing
employees as 
  (select 'John' as employee union all
   select 'Jack'             union all
   select 'April'            union all
   select 'Sonny'            union all
   select 'Jill'             union all
   select 'Jane'             
  )
,

-- here's the trick: 

-- first we define a code_num as row_number for each color
c as
(select *, 
        row_number() over() as color_num 
   from colors
),

-- and then we define a code_num for each employee as 
-- ((row_number-1) "mod" (colors' table count)) +1
e as 
(select *, 
        (((row_number() over())-1) % (select count(*) from colors))+1 as color_num 
   from employees
)

-- get both tables joined by color_num   
select e.employee, 
       c.color
  from e 
  join c on c.color_num = e.color_num

输出:

employee  color
---------------
    John    Red
    Jack  Green
   April   Blue
   Sonny    Red
    Jill  Green
    Jane   Blue