原文: SQL Server如何用SQL實作一批字元串的全部組合
在SQL Server中，如何用SQL去實作得到一批字元串的全部組合呢？這個是同僚在實際需求當中遇到的一個問題，他的具體需求如下所示：
傳入參數格式為'1,2,3,224,15,6' 'A,BC,GHT,TTY,B,E'
輸出的内容為分割後字元串的所有非排列組合
!-階乘，如！=5××××=120 (M!/(N!*((M-N)!)))
公式描述：組合數公式是從m個不同元素中，任取n(n≤m)個元素并成一組，叫做從m個不同元素中取出n個元素的一個組合；
從m個不同元素中取出n(n≤m)個元素的所有組合的個數，叫做從m個不同元素中取出n個元素的組合數。用符号c(m,n) 表示。
如果有5個數字那麼就是M=5 單個數字組合N=1 (M!/(N!*((M-N)!)))=5
是M=5 2個數字組合N=2 (M!/(N!*((M-N)!)))=10
是M=5 3個數字組合N=3 (M!/(N!*((M-N)!)))=10
是M=5 4個數字組合N=4 (M!/(N!*((M-N)!)))=5
是M=5 5個數字組合N=5 (M!/(N!*((M-N)!)))=1
可能全部的組合有 10 + 10 + 5+ 5 + 1 =31 種。傳入的參數分割後越多，組合數也就越龐大。
他最開始的實作方式就是用多層循環實作（此處就不貼代碼了），但是當要實作組合的字元串數量增多時，效率性能就下降得非常厲害，後面我參考一種寫法寫了下面SQL語句。
--建立輔助表SEQ_NUMBER CREATE TABLE SEQ_NUMBER(COL_NUM INT); DECLARE @Index INT =1; WHILE @Index <=32 BEGIN INSERT INTO SEQ_NUMBER VALUES(@Index); SET @Index +=1; END; GO --建立輔助表,用于儲存拆分後的字元串 CREATE TABLE SplitString(COL_NUM INT IDENTITY(1,1) ,VAL VARCHAR(32)); --建立函數 CREATE FUNCTION FN_GET_COMBINATIONS() RETURNS @OutTable TABLE(COL VARCHAR(32), VAL VARCHAR(32)) AS BEGIN DECLARE @Str VARCHAR(32)=''; DECLARE @Index INT =1; DECLARE @RowCount INT; SELECT @RowCount=COUNT(*) FROM SplitString; --注意，如果字元串分隔後有5個字元串(A,BC,GHT,TTY,B,E)，就使用12345， 如果分割後有6個字元串，就必須用123456,以此類推 WHILE @Index <= @RowCount BEGIN SET @Str = @Str + CAST(@Index AS VARCHAR(2)) SET @Index = @Index +1 END INSERT INTO @OutTable SELECT S.COL_NUM, T.VAL FROM SEQ_NUMBER S , SplitString T WHERE S.COL_NUM = T.COL_NUM ORDER BY T.COL_NUM; WHILE NOT EXISTS(SELECT 1 FROM @OutTable where COL = @Str) INSERT INTO @OutTable SELECT T3.COL + T2.COL, T3.VAL + T2.VAL FROM @OutTable AS T2,@OutTable AS T3 WHERE len(t3.col) = 1 and charindex(T3.COL,T2.COL) =0 and T2.COL > T3.COL RETURN; END 然後假如，我們需要實作字元串'1,2,3,4,5'中1,2,3,4,5的所有組合方式，那麼我們用下面SQL就能得到結果。如下所示，當然你也可以用諸如'A,BC,GHT,TTY,B,E'這樣的字元串去擷取字元串的所有組合。
DECLARE @String VARCHAR(200) DECLARE @SqlText VARCHAR(MAX) DECLARE @Index INT=1; SET @String='A,BC,GHT,TTY,B'; SET @SqlText='SELECT COL='''+ REPLACE(@STRING,',',''' UNION ALL SELECT ''')+'''' --清空舊資料，儲存需要進行組合的字元串資料。 TRUNCATE TABLE SplitString; INSERT INTO SplitString EXEC (@SqlText) SELECT DISTINCT CHAR_VAL , COL_NUM ,LEN(COL_NUM) AS STR_CNT FROM dbo.FN_GET_COMBINATIONS() WHERE LEN(COL_NUM) >= 1 ORDER BY LEN(COL_NUM) ,COL_NUM; 
後面測試發現，當組合的字元串數量超過或等于10個時，這個函數就有問題了(有興趣的可以自行測試，例如傳入的參數為’1,2,3,4,5,6,7,8,9,10’)。是以又對這個進行了一番修改。目前最多支援擷取26個字元串的全部組合，這個已經完全滿足業務需要了。如果再需要跟多的字元串組合，則還需修改函數。這個函數效率也是與需要組合的字元串個數有相關，如果組合11個字元串，基本上需要3~4秒的時間，如果組合的字元串個數越多，則所需時間越多。當然，如果組合的字元串個數7~8個，幾乎就是1秒内。
CREATE TABLE SEQ_CHARACTER(COL_NUM INT ,COL_CHAR VARCHAR(36)); INSERT INTO SEQ_CHARACTER SELECT 1 ,'A' UNION ALL SELECT 2 ,'B' UNION ALL SELECT 3 ,'C' UNION ALL SELECT 4 ,'D' UNION ALL SELECT 5 ,'E' UNION ALL SELECT 6 ,'F' UNION ALL SELECT 7 ,'G' UNION ALL SELECT 8 ,'H' UNION ALL SELECT 9 ,'I' UNION ALL SELECT 10,'J' UNION ALL SELECT 11,'K' UNION ALL SELECT 12,'L' UNION ALL SELECT 13,'M' UNION ALL SELECT 14,'N' UNION ALL SELECT 15,'O' UNION ALL SELECT 16,'P' UNION ALL SELECT 17,'Q' UNION ALL SELECT 18,'R' UNION ALL SELECT 19,'S' UNION ALL SELECT 20,'T' UNION ALL SELECT 21,'U' UNION ALL SELECT 22,'V' UNION ALL SELECT 23,'W' UNION ALL SELECT 24,'X' UNION ALL SELECT 25,'Y' UNION ALL SELECT 26,'Z' CREATE TABLE SplitString(COL_NUM INT IDENTITY(1,1) ,VAL VARCHAR(32)); CREATE FUNCTION FN_GET_COMBINATIONS() RETURNS @OutTable TABLE(COL_NUM VARCHAR(32), CHAR_VAL VARCHAR(32)) AS BEGIN DECLARE @Str VARCHAR(32)=''; DECLARE @Index INT =1; DECLARE @RowCount INT; SELECT @RowCount=COUNT(*) FROM SplitString; --注意，如果字元串分隔後有5個字元串(A,BC,GHT,TTY,B,E)，就使用12345， 如果分割後有6個字元串，就必須用123456,以此類推 WHILE @Index <= @RowCount BEGIN SELECT @Str = @Str + LTRIM(RTRIM(COL_CHAR)) FROM SEQ_CHARACTER WHERE COL_NUM=@Index SET @Index = @Index +1 END INSERT INTO @OutTable SELECT S.COL_CHAR, T.VAL FROM SEQ_CHARACTER S , SplitString T WHERE S.COL_NUM = T.COL_NUM ORDER BY T.COL_NUM; WHILE NOT EXISTS(SELECT 1 FROM @OutTable where COL_NUM = @Str) INSERT INTO @OutTable SELECT T3.COL_NUM + T2.COL_NUM, T3.CHAR_VAL + T2.CHAR_VAL FROM @OutTable AS T2,@OutTable AS T3 WHERE len(T3.COL_NUM) = 1 and charindex(T3.COL_NUM,T2.COL_NUM) =0 and T2.COL_NUM > T3.COL_NUM RETURN; END 測試腳本如下：
DECLARE @String VARCHAR(200) DECLARE @SqlText VARCHAR(MAX) DECLARE @Index INT=1; SET @String='A,B,C,D,E,F,G,H,G,H,I'; SET @SqlText='SELECT COL='''+ REPLACE(@STRING,',',''' UNION ALL SELECT ''')+'''' --清空舊資料，儲存需要進行組合的字元串資料。 TRUNCATE TABLE SplitString; INSERT INTO SplitString EXEC (@SqlText) SELECT DISTINCT CHAR_VAL , COL_NUM ,LEN(COL_NUM) AS STR_CNT FROM dbo.FN_GET_COMBINATIONS() WHERE LEN(COL_NUM) >= 1 ORDER BY LEN(COL_NUM) ,COL_NUM; ![MyBatis-Plus 之AR模式[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)