SQL Server 结果是所有可能的排序

Question

我是这个论坛的新手，正在寻求以下问题的帮助。 我希望从结果集中获取所有可能的排序。 例如

Column1
-------
val1
val2
val3

预期成果

RowNumCoumn   Column1
-----------   -------
1              Val1         
2              Val2
3              Val3
4              Val1
5              Val3
6              Val2
7              Val2
8              Val1
9              Val3
10             Val2
11             Val3
12             Val1
13             Val3
14             Val1
15             Val2
16             Val3
17             Val2
18             Val1

Answer 1

在我看来，这是一个非常困难的问题。 我的解决方案使用两个递归 CTES：

with t as (select * from (values ('a'), ('b'), ('c')) v(col)),
tn as (
select row_number() over (order by col) as n, col
from t
),
cte as (
select ',' + cast(n as varchar(max)) + ',' as ordering,
cast(col as varchar(max)) + ',' as columns,
1 as lev
from tn
union all
select cte.ordering+ cast(tn.n as varchar(max)) + ',' ,
cast(cte.columns + tn.col as varchar(max)) + ',',
lev + 1
from cte join
tn
on cte.ordering not like '%,' + cast(tn.n as varchar(max)) + ',%'
),
orderings as (
select top (1) with ties cte.*
from cte
order by lev desc
),
splits as (
select ordering, columns,
left(columns, charindex(',', columns) - 1) as val,
stuff(columns, 1, charindex(',', columns) , '')  as rest,
1 as lev
from orderings
union all
select ordering, columns,
left(rest, charindex(',', rest) - 1) as val,
stuff(rest, 1, charindex(',', rest), '') as rest,
lev + 1
from splits
where rest like '%,%'
)
select * 
from splits;
order by ordering, lev;

(当然，逗号是值的任意分隔符。 如果值已经有逗号，您可以将其更改为所需的任何内容(。

第一个递归 cte(cte(计算所有可能的排序，但每个排序只有一行。 它遍历数据，构建所有可能的组合，一次一个元素。 cteorderings实际上是具有完整组合(在本例中长度为 3(的 cte。 您可能会发现orderings有您正在寻找的信息。

第二个递归 cte (splits( 然后展开拆分以将结果返回行中。

这是一个有趣的练习，但我不会在包含几行以上的表上运行此类代码。 输出行数为 n * n！(n 阶乘(。 这增长得相当快。

我还应该指出，这种类型的逻辑可能更适合在应用程序级别实现，而不是在 SQL 中实现。 但这是一个有趣的SQL问题。 毕竟，解决方案是n * n的问题并不多！原始表中的行。

Answer 2

考虑到预期的结果，这是一个有点奇怪的挑战。

但是您可以通过自联接来执行此操作。
可用于查找 3 个值的所有组合。

然后取消透视该结果以获得预期结果。

示例代码段：

declare @Table table (Column1 varchar(30));
insert into @Table (Column1) values
('val1'),
('val2'),
('val3');
select 
row_number() over (order by rn, Col) as RowNumColumn,
Val as Column1
from
(
select 
t1.Column1 as Col1, 
t2.Column1 as Col2, 
t3.Column1 as Col3,
row_number() over (order by t1.Column1, t2.Column1, t3.Column1) as rn
from @Table as t1
left join @Table as t2 on t2.Column1 != t1.Column1
left join @Table as t3 on t3.Column1 not in (t1.Column1, t2.Column1)
) src
UNPIVOT (
Val 
FOR Col IN ([Col1], [Col2], [Col3])
) as unpvt
order by RowNumColumn;