T-SQL中结果集的powerset(所有组合) [英] powerset(all combinations) of a resultset in T-SQL
本文介绍了T-SQL中结果集的powerset(所有组合)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要一个t-sql代码来获取结果集的幂集.
I need a t-sql code to get powerset of a resultset.
示例输入:
ColumnName
1
2
3
Example Output(one columns as nvarchar) :
1
2
3
1,2
1,3
2,3
1,2,3
输出集可能包含重复的值,例如(1,3 vs 3,1).
Output set may contain duplicate values such as (1,3 vs 3,1).
推荐答案
您可以使用此功能并获得完整的功率设置.
享受它.
You can use this one and get full powerset.
Enjoy it.
--EXEC PowerSet 'A,B,C,D'
Create PROCEDURE PowerSet(@Members NVARCHAR(64))
As
Begin
Declare @SubSet NVARCHAR(Max),
@SubSetCount int,
@Counter1 int,
@Counter2 int,
@ID int
Create table #Members
(
ID int IDENTITY(1,1) PRIMARY KEY,
Member NVARCHAR(50)
)
Create table #PowerSets
(
SubSet NVARCHAR(50)
)
-- Start Convert @Members To Table #Members
Set @Members=@Members+','
While @Members<>''
Begin
Insert Into #Members(Member)
Values(Left(@Members,CHARINDEX(',',@Members)-1))
Set @Members=RIGHT(@Members,LEN(@Members)-CHARINDEX(',',@Members))
End
-- End Convert @Members To Table #Members
-- Start Calculat PowerSet
Set @SubSetCount=POWER(2,(Select Count(*) From #Members))
Set @Counter1=0
while @Counter1<@SubSetCount
Begin
Set @Counter2=@Counter1
Set @ID=1
Set @SubSet='{'
While @Counter2>0
Begin
If CONVERT(char(1), @Counter2 % 2)=1
Set @SubSet=@SubSet+iif(@SubSet<>'{',',','')+(Select Member From #Members Where ID=@ID)
Set @Counter2 = CONVERT(int, (@Counter2 / 2))
Set @ID=@ID+1
End
Set @Counter1=@Counter1+1
Insert into #PowerSets(SubSet) values(@SubSet+'}')
End
-- End Calculat PowerSet
Select *
From #PowerSets
Order By Len(SubSet),SubSet
Drop Table #PowerSets
Drop Table #Members
End
这篇关于T-SQL中结果集的powerset(所有组合)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文