字符拆分排列组合

现有若干数字,将所有的排列组合可能均显示出来.
例如:4个数字
1,2,3,4
1
2
3
4
1,2
1,3
1,4
2,3
2,4
3,4
1,2,3
1,2,4
1,3,4
2,3,4
1,2,3,4
如何存储不限.

 

-->蓉儿

select top 100 id=identity(int,1,1) into # from sysobjects
declare @s nvarchar(100)
set @s='1,2,3,4'
select col=substring(@s,id,charindex(',',@s+',',id)-id)
into #1
from #
where charindex(',',','+@s,id)=id

insert #1 select @s

while @@rowcount>0
insert #1
select distinct left(col,len(col)-1)
from
(
    select col=stuff(replace(','+b.col+',',','+a.col+',',','),1,1,'')
    from #1 a,#1 b
    where charindex(','+a.col+',',','+b.col+',')>0 and a.col<>b.col
) t
where left(col,len(col)-1) not in (select col from #1)


select * from #1 order by len(col)
/*
col
---------
1
2
3
4
1,2
1,3
1,4
2,3
2,4
3,4
1,2,3
1,2,4
1,3,4
2,3,4
1,2,3,4

(15 行受影响)
*/
drop table #
drop table #1

declare @s nvarchar(20) set @s='1,2,3,4' select col=substring(@s,number,charindex(',',@s+',',number)-number) into #1 from master..spt_values where type='p' and charindex(',',','+@s,number)=number insert #1 select @s while @@rowcount>0 insert #1 select distinct left(col,len(col)-1) from ( select col=stuff(replace(','+b.col+',',','+a.col+',',','),1,1,'') from #1 a,#1 b where charindex(','+a.col+',',','+b.col+',')>0 and a.col<>b.col ) t where left(col,len(col)-1) not in (select col from #1) select * from #1 order by len(col) drop table #1 /* col -------------------- 1 2 3 4 1,2 1,3 1,4 2,3 2,4 3,4 1,2,3 1,2,4 1,3,4 2,3,4 1,2,3,4 (15 行受影响) */