sql - 压缩/重复连接？

Question

假设我有一个带有类型列的简单文档表:

Documents
Id  Type
1   A
2   A
3   B
4   C
5   C
6   A
7   A
8   A
9   B
10  C

用户有权访问不同类型的文档:

Permissions
Type    User
A       John
A       Jane
B       Sarah
C       Peter
C       John
C       Mark

我需要将这些文档作为任务分发给用户:

Tasks
Id  T DocId UserId
1   A   1   John
2   A   2   Jane
3   B   3   Sarah
4   C   4   Peter
5   C   5   John
6   A   6   John
7   A   7   Jane
8   A   8   John
9   B   9   Sarah
10  C   10  Mark

我怎么做？我如何获得任务？

Answer 1

您可以枚举行，然后使用模运算进行匹配:

with d as (
      select d.*,
             row_number() over (partition by type order by newid()) as seqnum,
             count(*) over (partition by type) as cnt
      from documents d
     ),
      u as (
       select u.*,
              row_number() over (partition by type order by newid()) as seqnum,
              count(*) over (partition by type) as cnt
       from users u
      )
select d.*
from d join
     u
     on d.type = u.type and
        u.seqnum = (d.seqnum % u.cnt) + 1