T-SQL "动态" 连接

Question

给定以下具有单个char（1）列的SQL Server表：

Value
------
'1'
'2'
'3'

我该如何在 T-SQL 中获得以下结果？

Result
------
'1+2+3'
'1+3+2'
'2+1+3'
'2+3+1'
'3+2+1'
'3+1+2'

这也需要是动态的，所以如果我的表只有行'1'和'2'，我期望:

Result
------
'1+2'
'2+1'

似乎我应该能够使用CROSS JOIN来实现这个目标，但由于我事先不知道会有多少行，所以我不确定需要多少次CROSS JOIN。

SELECT a.Value + '+' + b.Value
FROM MyTable a
CROSS JOIN MyTable b
WHERE a.Value <> b.Value

在任何给定时间，总是少于10行（实际上更像是1-3行）。我能否在SQL Server中即时完成此操作？

编辑：理想情况下，我希望这可以在单个存储过程中完成，但如果必须使用另一个存储过程或某些用户定义的函数来完成此操作，我也可以接受。

- Dave Ziegler

---

仅唯一值？ - Hart CO

@GoatCO 是的，仅限不同的值。 - Dave Ziegler

这位网友似乎正在做你所需要的事情，本质上是这样的：https://dev59.com/1HA65IYBdhLWcg3wrgsa - thyme

数字是否总是连续不间断的？ - Yuriy Galanter

@YuriyGalanter 不，它们不必是连续的，实际上它们是字符，可以是字母或数字。 - Dave Ziegler

Answer 1

这个SQL将计算不重复的排列组合：

WITH recurse(Result, Depth) AS
(
    SELECT CAST(Value AS VarChar(100)), 1
    FROM MyTable

    UNION ALL

    SELECT CAST(r.Result + '+' + a.Value AS VarChar(100)), r.Depth + 1
    FROM MyTable a
    INNER JOIN recurse r
    ON CHARINDEX(a.Value, r.Result) = 0
)

SELECT Result
FROM recurse
WHERE Depth = (SELECT COUNT(*) FROM MyTable)
ORDER BY Result

如果 MyTable 包含9行，计算需要一些时间，但会返回362,880行。

更新说明：

WITH 语句用于定义一个递归公共表达式。实际上，WITH 语句会执行多次 UNION 循环，直到递归结束。

SQL 的第一部分设置了起始记录。假设在 MyTable 中有3行，分别命名为'A'、'B'和'C'，则将生成这些行：

    Result     Depth
    ------     -----
    A          1
    B          1
    C          1

然后下一个 SQL 块执行第一层递归：

    SELECT CAST(r.Result + '+' + a.Value AS VarChar(100)), r.Depth + 1
    FROM MyTable a
    INNER JOIN recurse r
    ON CHARINDEX(a.Value, r.Result) = 0

这将获取到目前已生成的所有记录（即在recurse表中），并再次将它们与MyTable中的所有记录进行连接。 ON子句过滤了MyTable中的记录列表，仅返回此行排列中尚不存在的记录。 这将导致以下行：

    Result     Depth
    ------     -----
    A          1
    B          1
    C          1
    A+B        2
    A+C        2
    B+A        2
    B+C        2
    C+A        2
    C+B        2

然后递归再次循环，给出这些行：

    Result     Depth
    ------     -----
    A          1
    B          1
    C          1
    A+B        2
    A+C        2
    B+A        2
    B+C        2
    C+A        2
    C+B        2
    A+B+C      3
    A+C+B      3
    B+A+C      3
    B+C+A      3
    C+A+B      3
    C+B+A      3

此时，递归停止，因为UNION不再创建任何行，因为CHARINDEX始终为0。

最后一个SQL过滤掉所有计算出的Depth列与MyTable中记录数相匹配的结果行。这会剔除除了最后一层递归生成的行之外的所有行。因此，最终结果将是这些行:

    Result
    ------
    A+B+C
    A+C+B
    B+A+C
    B+C+A
    C+A+B
    C+B+A

- David

---

哇，这太酷了。它是如何工作的？看起来像魔法。 - Yuriy Galanter

我现在正在检查这个，它似乎正在做我想要的事情 :) 我很快会回报... - Dave Ziegler

我刚刚在 SQLFiddle 上玩了一下...不错 :) - Eli Gassert

@David，你能否认真地更新一下答案，并解释一下这个“巫术”是怎么回事吗？ - Yuriy Galanter

我在@bhamby的解释中添加了一些示例。 - David

Answer 2

您可以使用递归CTE来实现这一点：

with t as (
      select 'a' as value union all
      select 'b' union all
      select 'c'
     ),
     const as (select count(*) as cnt from t),
     cte as (
      select cast(value as varchar(max)) as value, 1 as level
      from t
      union all
      select cte.value + '+' + t.value, 1 + level
      from cte join
           t 
           on '+'+cte.value+'+' not like '%+'+t.value+'+%' cross join
           const
      where level <= const.cnt
     )
select cte.value
from cte cross join
     const
where level = const.cnt;

- Gordon Linoff

---

如果是1、2、3、4呢？ - Yuriy Galanter

确切地说，那就是我卡住的地方 :( - Dave Ziegler

@DaveZiegler...请查看修订后的答案。 - Gordon Linoff