create function F_strSpit12(@s varchar(200)) 
returns @t table(col varchar(5))
as
begin

    insert into @t select substring(@s,number,1)
    from v_getnumber where number <=len(@s)
    while not exists(select 1 from @t where col = @s)
    insert @t
 --   output inserted.*--inserted.col, inserted.col
 --   into @tt
    SELECT T3.COL + T2.COL FROM @t AS T2,@t AS T3 WHERE len(t3.col) = 1 and charindex(T3.COL,T2.COL) =0 and T2.COL > T3.COL

    return
end
 go
declare @s varchar(200)
set @s='ABCD'
select distinct col,len(col) 
from dbo.F_strSpit12(@s)
where len(col) > 1 
order by len(col),col

/*
col   
----- -----------
AB    2
AC    2
AD    2
BC    2
BD    2
CD    2
ABC   3
ABD   3
ACD   3
BCD   3
ABCD  4

(11 行受影响)
*/

------解决方案--------------------
SQL code
;WITH t1(name) AS
(
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' 
)
,t2 AS
(
SELECT a.name AS name1 ,b.name AS  name2 FROM t1 AS a ,t1 AS b
WHERE a.name <b.name 
)
,t3 AS
(
SELECT a.name,b.name1, b.name2 FROM t1 AS a ,t2 AS b
WHERE a.name <b.name1 
)
SELECT name FROM t1 
UNION ALL
SELECT name1+name2 FROM t2
UNION ALL
SELECT name+name1+name2 FROM t3
/*
name
----
A
B
C
D
AB
AC
BC
AD
BD
CD
ABC
ABD
ACD
BCD

(14 行受影响)

*/

------解决方案--------------------

SQL code
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--排列组合

--DEBUG:exec [dbo].[Arrange] @input='111,222,333'

--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 < @en