作为SQL查询，按Id排列和组合具有不同组的SUM

Question

我需要根据不同组的Id进行排列和组合:作为SQL QUERY

CREATE TABLE TestTable2([Id] [int] NULL, [Group] [varchar](50) NULL, [PeriodStart] [varchar](50) NULL) ON [PRIMARY]
INSERT INTO TestTable2([Id], [Group], [PeriodStart]) VALUES (1, 'Group1', 'date1a')<br/>
INSERT INTO TestTable2([Id], [Group], [PeriodStart]) VALUES (1, 'Group1', 'date1b')<br/>
INSERT INTO TestTable2([Id], [Group], [PeriodStart]) VALUES (1, 'Group1', 'date1c')<br/>
INSERT INTO TestTable2([Id], [Group], [PeriodStart]) VALUES (1, 'Group2', 'date2a')<br/>
INSERT INTO TestTable2([Id], [Group], [PeriodStart]) VALUES (1, 'Group2', 'date2b')<br/>
INSERT INTO TestTable2([Id], [Group], [PeriodStart]) VALUES (1, 'Group3', 'date3a')<br/>
INSERT INTO TestTable2([Id], [Group], [PeriodStart]) VALUES (1, 'Group3', 'date3b')<br/>
INSERT INTO TestTable2([Id], [Group], [PeriodStart]) VALUES (1, 'Group3', 'date3c')<br/>

表中数据:

**Id -- Group -- PeriodStart**<br/>
1 -- Group1 -- date1a<br/>
1 -- Group1 -- date1b<br/>
1 -- Group1 -- date1c<br/>
1 -- Group2 -- date2a<br/>
1 -- Group2 -- date2b<br/>
1 -- Group3 -- date3a<br/>
1 -- Group3 -- date3b<br/>
1 -- Group3 -- date3c<br/>

注意:可以有任意数量的组，PeriodStart是一个日期时间

所需的输出为:应该是3*2*3不同组行的18个组合(即这里的Group1, Group2和Group3)，其最大日期为PeriodStart:

**Id -- MaximumPeriodStartDate**<br/>
1 -- MAX OF (date1a, date2a, date3a)<br/>
1 -- MAX OF (date1a, date2b, date3a)<br/>
1 -- MAX OF (date1a, date2a, date3b)<br/>
1 -- MAX OF (date1a, date2b, date3b)<br/>
1 -- MAX OF (date1a, date2a, date3c)<br/>
1 -- MAX OF (date1a, date2b, date3c)<br/>
1 -- MAX OF (date1b, date2a, date3a)<br/>
1 -- MAX OF (date1b, date2b, date3a)<br/>
1 -- MAX OF (date1b, date2a, date3b)<br/>
1 -- MAX OF (date1b, date2b, date3b)<br/>
1 -- MAX OF (date1b, date2a, date3c)<br/>
1 -- MAX OF (date1b, date2b, date3c)<br/>
1 -- MAX OF (date1c, date2a, date3a)<br/>
1 -- MAX OF (date1c, date2b, date3a)<br/>
1 -- MAX OF (date1c, date2a, date3b)<br/>
1 -- MAX OF (date1c, date2b, date3b)<br/>
1 -- MAX OF (date1c, date2a, date3c)<br/>
1 -- MAX OF (date1c, date2b, date3c)<br/>

Answer 1

查看此SQL文件

SELECT SQ1.[ID]
        ,SQ1.[VALUE]+'+'+SQ2.[VALUE]+'+'+SQ3.[VALUE] AS COMBOS
FROM
(
SELECT [id],[VALUE]
FROM TESTTABLE2
WHERE [GROUP] = 1
) SQ1
INNER JOIN
(
SELECT [id],[VALUE]
FROM TESTTABLE2
WHERE [GROUP] = 2
) SQ2
ON SQ1.[ID]= SQ2.[ID]
INNER JOIN
(
SELECT [id],[VALUE]
FROM TESTTABLE2
WHERE [GROUP] = 3
) SQ3
ON SQ1.[ID]= SQ3.[ID]

Answer 2

我将变量重命名为大多数数据库中不保留关键字的东西:

select t1.id, t2.id, t3.id, t1.ValStr+'+'+t2.ValStr+'+'+t3.ValStr
from @TestTable2 t1 join
     @TestTable2 t2
     on t1.TheGroup < t2.TheGroup  join
     @TestTable2 t3
     on  t2.TheGroup < t3.TheGroup 

我使用SQL Server语法的字符串连接，因为它看起来像你正在使用该数据库。

我不能很容易地想到一种在sql查询中处理任意数量的组的方法。但是，您可以使用以下命令处理"最多"数量的组:

select t1.id, t2.id, t3.id,
       stuff((coalesce('+'+t1.ValStr), '')+coalesce('+'+t2.ValStr, '') +
              coalesce('+'+t3.ValStr, '')+ . . .
             ), 1, 1, '')
from @TestTable2 t1 left outer join
     @TestTable2 t2
     on t1.TheGroup < t2.TheGroup left outer join
     @TestTable2 t3
     on  t2.TheGroup < t3.TheGroup . . 

您将继续左外连接到最大数量的组，并相应地修复select子句。

结果和你想要的有点不同。这会产生最多n个组的所有组合，而不是恰好n个组。所以第一个会产生:(3 + 2 + 3)[1的组合]+(3 * 2 + 2 * 3 + 3 * 3)[2的组合]+(3 * 2 * 3)。你可以通过使用这个相当麻烦的where子句来解决这个问题:

where ((case when t1.thegroup is not null then 1 else 0 end)+
       (case when t2.thegroup is not null then 1 else 0 end)+
       (case when t3.thegroup is not null then 1 else 0 end)+
       . . .
      ) = (select count(distinct thegroup) from @TestTable2)

如果你的组是按顺序编号的，没有间隔，你也可以这样写:

select t1.id, t2.id, t3.id,
       stuff((coalesce('+'+t1.ValStr), '')+coalesce('+'+t2.ValStr, '') +
              coalesce('+'+t3.ValStr, '')+ . . .
             ), 1, 1, '')
from @TestTable2 t1 left outer join
     @TestTable2 t2
     on t2.TheGroup = t1.TheGroup+1 left outer join
     @TestTable2 t3
     on t3.TheGroup = t2.TheGroup+1 . . .
where t1.TheGroup = 1

实际上，即使您的组不是按顺序编号的，您也可以使用dense_rank()进行安排，并从那里开始:

with t as (
      select t.*, dense_rank() over (order by [Group]) as TheGroup
      from @TestTable2
     )
select t1.id, t2.id, t3.id,
       stuff((coalesce('+'+t1.ValStr), '')+coalesce('+'+t2.ValStr, '') +
              coalesce('+'+t3.ValStr, '')+ . . .
             ), 1, 1, '')
from t t1 left outer join
     t t2
     on t2.TheGroup = t1.TheGroup+1 left outer join
     t t3
     on t3.TheGroup = t2.TheGroup+1 . . .
where t1.TheGroup = 1

另一种方法是使用递归cte。如果没有这些，这可能是最好的纯sql方法。