SQLServer排列组合,六个字母中取三个排列组合
2019-03-25 本文已影响0人
故事的开头是个码农
六个字母abcdef中取三个任何组合,例如abc,acb,efc求最后的结果
DECLARE @T TABLE(
COL1 VARCHAR(100)
)
INSERT INTO @T
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E' UNION ALL
SELECT 'F'
SELECT * FROM (SELECT COL1 FROM @T
UNION ALL
SELECT T1.COL1+T2.COL1 'COL1' FROM @T T1
FULL JOIN @T T2 ON T1.COL1<>T2.COL1
UNION ALL
SELECT T1.COL1+T2.COL1 'COL1' FROM @T T1
FULL JOIN (
SELECT T1.COL1+T2.COL1 'COL1' FROM @T T1
FULL JOIN @T T2 ON T1.COL1<>T2.COL1
) T2 ON T1.COL1<>T2.COL1
UNION ALL
SELECT T1.COL1+T2.COL1 'COL1' FROM @T T1
FULL JOIN (
SELECT T1.COL1+T2.COL1 'COL1' FROM (
SELECT T1.COL1+T2.COL1 'COL1' FROM @T T1
FULL JOIN @T T2 ON T1.COL1<>T2.COL1
) T1
FULL JOIN @T T2 ON T1.COL1<>T2.COL1
) T2 ON T1.COL1<>T2.COL1) A where LEN(COL1)=3