求一条sql语句

[yyj135]

有表结构如下：
ID Code
1 A
2 B
3 C
4 D

要求输出如下结果
A
B
C
D

AB
AC
AD

BC
BD

CD

ABC
ABD

BCD

ABCD

[泪寒雪]

引用 4 楼 Tiger_Zhao 的回复:

;WITH TB(ID,Code)AS(
	SELECT 1,'A'
	UNION ALL SELECT 2,'B'
	UNION ALL SELECT 3,'C'
	UNION ALL SELECT 4,'D'
)
,r(str,lastID) AS (
    SELECT Convert(varchar(max), Code),
           ID
      FROM tb
    UNION ALL
    SELECT Convert(varchar(max), r.str+tb.code),
           tb.ID
      FROM r
      JOIN tb
        ON tb.ID > r.lastID
)
SELECT * FROM r ORDER BY LEN(str),str

str
----
A
B
C
D
AB
AC
AD
BC
BD
CD
ABC
ABD
ACD
BCD
ABCD

(15 行受影响)

楼上正解

[还在加载中灬]

如果四个的话，直接这样

;WITH TB(ID,Code)AS(
	SELECT 1,'A'
	UNION ALL SELECT 2,'B'
	UNION ALL SELECT 3,'C'
	UNION ALL SELECT 4,'D'
)
SELECT Code
FROM TB
UNION ALL
SELECT T1.Code+T2.Code
FROM TB T1
	JOIN TB T2 ON T1.ID<T2.ID
UNION ALL
SELECT T1.Code+T2.Code+T3.Code
FROM TB T1
	JOIN TB T2 ON T1.ID<T2.ID
	JOIN TB T3 ON T2.ID<T3.ID
UNION ALL
SELECT T1.Code+T2.Code+T3.Code+T4.Code
FROM TB T1
	JOIN TB T2 ON T1.ID<T2.ID
	JOIN TB T3 ON T2.ID<T3.ID
	JOIN TB T4 ON T3.ID<T4.ID

不确定的话，就动态处理

[yyj135]

现在这个就是不确定，请问动态的话如何处理，谢谢！

[还在加载中灬]

DECLARE @SQL VARCHAR(MAX),@TABLE VARCHAR(MAX),@COLUMN VARCHAR(MAX)
SET @SQL=''
DECLARE @MAX INT,@INDEX INT
SELECT @MAX=COUNT(1)FROM TB
SET @INDEX=1
SET @SQL='SELECT Code FROM TB'
WHILE @INDEX<@MAX
BEGIN
	SET @TABLE='TB T1'
	SET @COLUMN='T1.Code'
	SET @INDEX=@INDEX+1
	SELECT @TABLE=@TABLE+' JOIN TB T'+CAST(number AS VARCHAR(10))+' ON T'+CAST(number-1 AS VARCHAR(10))+'.ID<T'+CAST(number AS VARCHAR(10))+'.ID'
		,@COLUMN=@COLUMN+'+T'+CAST(number AS VARCHAR(10))+'.Code'
	FROM master..spt_values
	WHERE type='P'AND number>1 AND number<=@INDEX
	SET @SQL=@SQL+' UNION ALL SELECT '+@COLUMN+' FROM '+@TABLE
END
--PRINT @SQL
EXEC(@SQL)

[Tiger_Zhao]

;WITH TB(ID,Code)AS(
	SELECT 1,'A'
	UNION ALL SELECT 2,'B'
	UNION ALL SELECT 3,'C'
	UNION ALL SELECT 4,'D'
)
,r(str,lastID) AS (
    SELECT Convert(varchar(max), Code),
           ID
      FROM tb
    UNION ALL
    SELECT Convert(varchar(max), r.str+tb.code),
           tb.ID
      FROM r
      JOIN tb
        ON tb.ID > r.lastID
)
SELECT * FROM r ORDER BY LEN(str),str

str
----
A
B
C
D
AB
AC
AD
BC
BD
CD
ABC
ABD
ACD
BCD
ABCD

(15 行受影响)