SQL Server實現group_concat功能的詳細範例

2022-08-26 18:05:51

一、實現

#tmp表內容如下：

實現group_concat的sql語句為：

Select
　　RegionID，
　　STUFF(
　　　　　　　(
　　　　　　　　　SELECT ',' + T.c1
　　　　　　　　　FROM #tmp T
　　　　　　　　　WHERE A.regionid = T.regionid
　　　　　　　　　FOR XML PATH('')
　　　　　　　), 1, 1, ''
　　　　　) as group_concat 
FROM #tmp A
Group by  RegionID

實現效果如下：

二、原理分析

2.1、FOR XML PATH的作用

FOR XML PATH 的作用是將查詢結果集以XML形式展現，將多行的結果，展示在同一行，例如：

select c1 from #tmp where RegionID = 41653

其結果集如下：

select c1 from #tmp where RegionID = 41653 FOR XML PATH('')

當sql語句加上 FOR XML PATH('') 後，其結果集輸出是：

具體輸出的字元如下：

<c1>30.326809</c1><c1>30.327982</c1><c1>30.347933</c1><c1>30.388104</c1><c1>30.392830</c1><c1>30.367931</c1><c1>30.368052</c1><c1>30.367842</c1><c1>30.357318</c1><c1>30.357349</c1><c1>30.357349</c1>

通過字元拼接後可以把xml資訊清除，並以指定的字元進行分割：

select ',' + c1 from #tmp where RegionID = 41653 FOR XML PATH('')

此時已基本達到group_concat的效果，但第一個字串有分隔符需要去掉。

2.2、STUFF函數

2.2.1、STUFF函數在本SQL的作用

我們使用STＵFF函數的目的是把第一個分隔符去掉。先看看效果：

上圖可以看到，STＵFF函數把字串“abcdefg”中的第一個字元“a”刪除。

使用該函數我們可以很輕鬆的把上圖得到的結果集去掉第一個逗號分隔符：

需要詳細瞭解STＵFF函數可繼續看該函數的語法，沒興趣的可以忽略。

2.2.2、STUFF函數語法

STUFF函數的作用是將字串插入到另一個字串中。它從第一個字串的開始位置刪除指定長度的字元，然後將第二個字串插入到第一個字串的開始位置。其語法為：

STUFF(character_expression , start , length , replaceWith_expression)

character_expression：字元資料的表示式，可以是常數、變數，也可以是字元列或二進位制資料列。

start：一個整數值(從1開始)，指定刪除和插入的開始位置。start的型別可以是bigint。

　　• 如果 start 為負或為零，則返回空字串。

　　• 如果 start 的長度大於第一個 character_expression，則返回空字串。

length：一個整數，指定要刪除的字元數。length的型別可以是 bigint。

　　• 如果 length 為負，則返回空字串。

　　• 如果 length 的長度大於character_expression，則最多可以刪除到character_expression 中的最後一個字元。

　　• 如果 length 為零，則不刪除字元直接在指定位置插入內容。

replaceWith_expression：字元資料的表示式，可以是常數、變數，也可以是字元列或二進位制資料列。此表示式從 start 開始替換 length 個字元的character_expression。

　　• 如果 replaceWith_expression 為 NULL，則在不插入任何內容的情況下刪除字元。

2.3、sql語分分析

2.3.1、一個簡單的group by

Select　RegionID 
FROM #tmp A
Group by RegionID

這個sql各位看官都十分熟悉，已經沒什麼好說的了。

2.3.2、在select語句後面加上子查詢

Select　RegionID,
　　　　(
　　　　　　SELECT ',' + T.c1
　　　　　　FROM #tmp T
　　　　　　WHERE A.regionid = T.regionid
　　　　　　FOR XML PATH('')
　　　　) 
FROM #tmp A
Group by RegionID

在上述簡單的group by語句基礎上加入一個select裡的子查詢，其結果如下：

在該子查詢中，當外層的group by返回結果集中的第一行RegionID為41653時，這個值被子查詢的where條件所使用，相當於：

SELECT ',' + T.c1
FROM #tmp T
WHERE T.regionid = 41653
FOR XML PATH('')

因為FOR XML PATH把多行記錄打平成一條記錄，因此此時的返回結果為：

接著第group by返回結果集中的第二行45761傳入該子查詢，依次類似上面描述的執行，直到所有外層的值遍歷完成。

2.3.3、去掉子查詢結果集的第一個分隔符

Select
　　RegionID，
　　STUFF(
　　　　　　　(
　　　　　　　　　SELECT ',' + T.c1
　　　　　　　　　FROM #tmp T
　　　　　　　　　WHERE A.regionid = T.regionid
　　　　　　　　　FOR XML PATH('')
　　　　　　　), 1, 1, ''
　　　　　) as group_concat 
FROM #tmp A
Group by  RegionID

利用STUFF函數，去掉了第一個逗號，完成了最終sql語句。