sql - 所有可能的组合未透视

Question

我一直在研究一个我不太明白的问题。我尝试了交叉连接、CTE、窗口函数等的不同组合，但永远无法完全实现。我也不想走动态 SQL 路线。有人可以帮忙吗？

给定一组可变的分组值，垂直产生所有可能的组合(派生组，值)

附加信息:

1. 任何 2 个组合都不应具有相同的值集，无论命令。示例:如果您已经有 (1,2)，则不要生成 (2,1)，如果 (1,2,3) 则没有 (1,3,2),(2,1,3),(2,3,1),(3,1,2),(3,2,1)
2. 同一组的值不应合并
3. 所有值都是唯一的，与组无关。的唯一原因初始分组是应用规则#2

例子:给定起始组和值

输入组值

• 1 8
• 2 7
• 2 9
• 3 1
• 3 6
• 3 3

产生这个输出

输出组值

• 1 8
• 2 7
• 3 9
• 4 1
• 5 6
• 6 3
• 7 8
• 7 7
• 8 8
• 8 9
• 9 8
• 9 1
• 10 8
• 10 6
• 11 8
• 11 3
• 12 7
• 12 1
• 13 7
• 13 6
• 14 7
• 14 3
• 15 9
• 15 1
• 16 9
• 16 6
• 17 9
• 17 3
• 18 8
• 18 7
• 18 1
• 19 8
• 19 7
• 19 6
• 20 8
• 20 7
• 20 3
• 21 8
• 21 9
• 21 1
• 22 8
• 22 9
• 22 6
• 23 8
• 23 9
• 23 3

这是生成输出的手动非垂直方法

CREATE TABLE #temp1 (GroupID INT, MyValue INT)

INSERT INTO #temp1 (GroupID, MyValue)
VALUES  (1,8),(2,7),(2,9),(3,1),(3,6),(3,3)

--1st set of possibilities
SELECT MyValue
FROM #temp1

--2nd set of possibilities
SELECT a.MyValue, b.MyValue
FROM #temp1 a
JOIN #temp1 b
ON a.GroupID < b.GroupID

--3rd set
SELECT a.MyValue, b.MyValue, c.MyValue
FROM #temp1 a
JOIN #temp1 b
ON a.GroupID < b.GroupID
JOIN #temp1 c
ON b.GroupID < c.GroupID

DROP TABLE #temp1 

我的问题是可以有可变数量的起始值考虑到这一点，我的输出需要在分组的垂直集中，所以我只返回 2 列。 1 将数字和数字本身组合在一起。对于这个特定的例子，应该有 46 行和 23 个不同的组，如上所示

我写了 CTE，我一直在修改，最后废弃了:

WITH    MyCTE
          AS (SELECT    1 AS Level, DENSE_RANK() OVER (ORDER BY GroupID, MyValue) AS DgroupID, GroupID, MyValue
              FROM      #temp1
              UNION ALL
              SELECT    a.Level + 1, DENSE_RANK() OVER (ORDER BY b.GroupID, b.MyValue), b.GroupID, b.MyValue
              FROM      MyCTE a
              JOIN      #temp1 b
                        ON a.GroupID < b.GroupID)

SELECT  DENSE_RANK() OVER (ORDER BY Level, DgroupID), MyValue
FROM    MyCTE 

明显的问题:

1) 我用来为每一行提供增量值的窗口函数没有按预期工作。这可能是由于 CTE 的工作方式所致。对性能有利，对我不利。 ROW_NUMBER 窗口函数做同样的事情。我想要做的就是在每次迭代中自动增加行，这样我就可以在表“unpivoted”时识别组。我相信 CTE 如此之快的原因是因为它们实际上是基于集合的操作，所以即使存在递归，我也不能依赖循环/迭代思维模式来产生预期结果。请随时纠正我所有的假设

2) 不旋转。我需要获取一组行并将列反透视为行，每行保留原始行的标识符以显示它们被组合在一起。 SQL Server 有一个很棒的命令，叫做 UNPIVOT，它对我一点帮助都没有，因为你需要知道在设计时要逆透视的列数。这样做的重点是能够提供可变数量的输入并产生可预测的输出

Answer 1

因此，您正在尝试将所有“第 1 组”值与所有“第 2 组”值和所有“第 3 组”值分组，但要防止重复，如您所述，例如:1,2 和 2,1。您的手动方法看起来不错，但是我不明白为什么您要比较组而不是“值”小于之前的值，这样......

SELECT a.MyValue, b.MyValue
FROM #temp1 a
JOIN #temp1 b
ON a.MyValue < b.MyValue AND a.GroupID <> b.GroupID

--3rd set
SELECT a.MyValue, b.MyValue, c.MyValue
FROM #temp1 a
JOIN #temp1 b
ON a.MyValue < b.MyValue AND a.GroupID <> b.GroupID 
JOIN #temp1 c
ON b.MyValue < c.MyValue AND a.GroupID <> c.GroupID AND b.GroupID <> c.GroupID

根据您的反馈，上述调整应该有效，只是需要额外的力量，因为它必须渗透到第 1 组、第 2 组以及第 2 组、第 1 组，因为第 1 组可能存在于第 2 组中，但是第 1 组的数量很少，只有 5。如果您总是限定 a.Group 小于 b.Group，那么您永远不会在第一个位置获得值 1，因为第 2 组大于第 1 组。

这对您的场景有意义吗？