如何在 tsql 中进行排列（基于集合）

Question

我有以下输入输出

PlayerID    MatchPlayed RunsMade
--------    ----------- --------
1           10              200
2           5               100
3           8               24
4           30              50

将为

Combined Players    Combined Match Played   Combined runs Made  
----------------    ---------------------   ------------------  
1                   10                      200         
1,2                 15                      300         
1,3                 18                      224
1,4                 40                      250
1,2,3               23                      324
1,2,4               45                      350
1,3,4               48                      274
1,2,3,4             53                      374
2                   5                       100
2,3                 13                      124
2,4                 35                      150
2,3,4               43                      174
3                   8                       24
3,4                 38                      74
4                   30                      50

组合比赛列是这些球员的比赛列值的总和。例如，对于组合比赛 1,2，组合比赛值是 10 + 5 = 15。

同样，组合得分是各个球员的得分列的总和。例如，对于同一示例，组合运行 MAde 列为 200 +100 =300。

谢谢

Answer 1

设置：

create table Input(PlayerId int, MatchPlayed int, RunsMade int)

insert Input
  select 1, 10, 200
  union all select 2, 5, 100
  union all select 3, 8, 24
  union all select 4, 30, 50

查询：

with cte(Combined, PlayerId, MatchPlayed, RunsMade)
as
(
    select cast(PlayerId as varchar(500)), PlayerId, MatchPlayed, RunsMade
    from Input
    union all
    select cast(cte.Combined + ',' + cast(inp.PlayerId as varchar) as varchar(500)), inp.PlayerId, inp.MatchPlayed + cte.MatchPlayed, inp.RunsMade + cte.RunsMade
    from cte
        join Input inp on
            cte.PlayerId < inp.PlayerId
)
select Combined, MatchPlayed, RunsMade
from cte
order by Combined