------------------------MSSQL中排列组合的实现------------------------------------

--排列组合

--执行方法:exec [Arrange] '1,2,3'

--input:1,2,3

--output:--1 --2 --3 --1,2 --1,3 --2,3 --1,2,3

Create PROCEDURE [dbo].[Arrange]

     @input varchar(100)

AS

BEGIN

    SET NOCOUNT ON

   

    if(len(@input)<1) return

    DECLARE @tableArrange Table

    (

        id int identity(1,1),

        arrangeValue varchar(100),

        maxid int,

        lenOfValue int

    )

    declare @split varchar(10)

    set @split = ','

    declare @startIndex int

    set @startIndex = 1

    declare @endIndex int

    set @endIndex = charindex(@split,@input,@startIndex)

    declare @items varchar(100)

    while(@endIndex<>0)

    begin

        set @items = Substring(@input,@startIndex,@endIndex-@startIndex)

        if len(@items)>0
       
        insert into @tableArrange(arrangeValue) values(@items)

        set @startIndex = @endIndex + 1

        set @endIndex = charindex(@split,@input,@startIndex)           

    end

    set @items = Substring(@input,@startIndex,len(@input)-@startIndex+1 )

    if len(@items)>0 insert into @tableArrange(arrangeValue) values(@items)

   

    update @tableArrange set maxid=id,lenOfValue=1

   

    declare @count int--

    declare @currentlen int   

    declare @value varchar(100)

    declare @valueInsert varchar(100)

    declare @start int

    declare @end int

    declare @i int

    declare @j int

    declare @maxid int

    declare @lenofvalue int

    select @count = max(id) from @tableArrange

    set @currentlen=1

    while(@currentlen<@count)

    begin

        select @start=min(id),@end=max(id) from @tableArrange where lenOfValue=@currentlen

        set @i=@start       

        while(@i<@end)

        begin

            select @maxid=maxid,@value=arrangeValue,@lenofvalue=lenofvalue from @tableArrange where id=@i

            set @j=@maxid+1

            while(@j<=@count)

            begin

                select @valueInsert=@value+','+arrangeValue from @tableArrange where id=@j

                insert into @tableArrange(arrangeValue,maxid,lenofvalue)

                    values(@valueInsert,@j,@lenofvalue+1)

                set @j=@j+1

            end

            set @i=@i+1

        end

        set @currentlen=@currentlen+1

    end

    select arrangeValue from @tableArrange

   
SET NOCOUNT ON

END

go

exec [Arrange]  '1,2,3,4'

go

drop proc [Arrange]

go

/*

arrangeValue
----------------------------------------------------------------------------------------------------
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

*/