如何从时间表中选择所有 PersonID、TimetableID 对的组合

with tbltmp(x, y) as (
    select * from (
        VALUES 
            (5215, 57), 
            (18943, 221), 
            (18943, 230), 
            (18943, 238), 
            (21488, 257), 
            (21488, 270), 
            (5215, 67), 
            (5215, 77), 
            (5215, 87), 
            (5215, 97)  
    ) as t(x, y)
),
rtbltmp as (
    select d.*, 
        dense_rank() over(order by x) as rnx,
        row_number() over(partition by x order by y) as rny,
        count(y) over(partition by x) as  cnt 
    from tbltmp d
)
, solution(x,y, rnx, rny, js) as (
    select x,y, rnx, rny, cast(concat('[' , x , ',' , y , ']') as varchar(max))
    from rtbltmp
    where rnx = 1 
    
    union all
    
    select d.x, d.y, d.rnx, d.rny, 
        concat(r.js, ',[',  d.x,  ',', d.y, ']')
    from solution r
    join rtbltmp d
        on 
            (d.rnx = r.rnx + 1)
),
jsolution(grp, js) as (
    select row_number() over(order by rnx), concat('[', js, ']') as js from solution
    where rnx = (select count(distinct x) from rtbltmp)
)
select * from jsolution ;

https://dbfiddle.uk/PLiK04hT

您可以使用 SQLServer 语法将 JSON 数组转换为行。

（以 ORACLE 版本为例：https://dbfiddle.uk/6VGG4Uad）

我们可以将这项任务视为对集合成员进行唯一组合 - 每个组合应该有每个集合的 1 个成员。

有了这些测试数据，我们有 3 组

set 5215(57, 67, 77, 87 97) - 5 members 
set 18943(221, 230, 238) - 3 members
set 21488(257, 270) - 2 members

因此组合数为 5*3*2=30 - totOver。
我们可以将每行乘以（生成系列）到 (totOver/rQty) 行。
例如

(5215,57) to (5215,57,1),(5215,57,2)...(5215,57,6)
(5215,67) to (5215,67,7),(5215,67,8)...(5215,67,12)
...
(5215,97) to (5215,97,25),(5215,97,26)...(5215,97,30)

三元组中的最后一个数字是组号。

查看示例

PrepRn输出

with  PersonCounts as(
   select PersonId,count(*)xCnt,row_number()over(order by count(*))grn
   from tblTimetable
   group by PersonId
)
,totCounts as(  -- recursion  to count 5*3*2
   select PersonId,xCnt,grn, xCnt totCnt 
   from PersonCounts    where grn=1
   union all
   select t.PersonId,t.xCnt,t.grn,r.totCnt*t.xCnt 
   from totCounts r 
   inner join PersonCounts t on t.grn=(r.grn+1)
)
,rcTot as( -- take max value 5*3*2=30
  select *,max(totCnt)over()totOver from totCounts
)
,PrepRn as(  -- calculate indexes for every row fromN - toN
  select PersonId,TimetableID,rQty,rnx
    ,(rnx-1)*totOver/rqty+1 fromN
    ,rnx*totOver/rqty toN
    ,totOver
  from(
    select t.PersonID,TimetableID
      ,row_number()over(partition by t.PersonId order by t.TimetableID) rnx
      ,count(*)over(partition by t.PersonID ) rQty
      ,totOver
    from tblTimetable t
    inner join rcTot t2 on t2.PersonID=t.PersonID
  )a
)
select t.PersonId,TimetableID,fromN rnx,toN ,nn.value grn
    ,rQty,totOver
from PrepRn t
cross apply generate_series(fromN,toN)nn
order by grn,PersonId;

.....

小提琴

小提琴

假设您可以从 SQL Server 2022 开始访问GENERATE_SERIES()，那么您不需要递归或连接，只需要一点数学运算和使用适当 ID 复制行。

就你的情况而言，有 30 种组合 ( 5 from person A* 3 from person B* 2 from person c== 30)

这意味着：

• 每行person A需要重复 6 次

• 每行person B需要重复 10 次

• 每行person C需要重复 15 次

每个重复项都需要不同的combo_id，例如person A：

这可以通过以下方式实现，GENERATE_SERIES(original_row_id, 30, 5)其中30是组合的总数，5是当前拥有的行数person。

Using CROSS APPLY means that we can generate a different series (generating different number of duplicates, with differing combination_d values) for each person and each of their rows.

CREATE TABLE timetable
(
  person_id      INT,
  timetable_id   INT
);


INSERT INTO
  timetable
VALUES
  (5215,   57), (5215,   67), (5215,   77), (5215,   87), (5215,   97),
  (18943, 221), (18943, 230), (18943, 238),
  (21488, 257), (21488, 270)
;

WITH
  summary (person_id, timetable_id, person_rows, person_row_ix)
AS
(
  SELECT
    person_id,
    timetable_id,
    COUNT(*) OVER (PARTITION BY person_id),
    CAST(ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY timetable_id) AS INT)
  FROM
    timetable
),
  mapping
AS
(
  SELECT
    *,
    -- PRODUCT AGGREGATE, in this case, 5*1*1*1*1 * 3*1*1 * 2*1 == 30
    CAST(
      ROUND(
        EXP(
          SUM(CASE WHEN person_row_ix = 1 THEN LOG(person_rows) ELSE 0 END)
          OVER ()
        ),
        0
      )
      AS INT
    )
      AS total_combinations
  FROM
    summary
)
SELECT
  value AS combination_id,
  mapping.*
FROM
  mapping
CROSS APPLY
  GENERATE_SERIES(
    person_row_ix,
    total_combinations,
    person_rows
  )
ORDER BY
  value,
  person_id

fiddle

尝试从原始表中删除 x 的匹配项，就像在这个 cte 查询中一样。您可以根据此逻辑准备一个存储过程，或者如果可能的话为 cte 查询添加递归性（抱歉，我在这方面不太擅长。）

with cte_a
as
(
    select
        x,
        min(y) as y1,
        count(*) as cnt
    from tblTmp
    group by x
),

cte_b
as
(
    select
        x,
        min(y) as y2,
        count(*) as cnt
    from tblTmp where y <> (select y1 from cte_a where cnt > 1)
    group by x
),

cte_c
as
(
    select
        x,
        min(y) as y,
        count(*) as cnt
    from tblTmp where y not in (select y1 from cte_a where cnt > 1 union select y2 from cte_b where cnt > 1)
    group by x
)



select * from cte_a union all select * from cte_b union all select * from cte_c

select *
from tblTimetable t1 cross join tblTimetable t2 cross join tblTimetable t3
where t1.PersonId < t2.PersonId and t2.PersonId < t3.PersonId;

进行三向交叉连接。由于不平等，某个人不会被选中多次。