萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 字串聚合三大技法:FOR XML PATH、STRING_AGG 與 CLR 聚合

GROUP BY 場景下將多行字串合併為單一值

需求:按 xh 分組,將各組 name 以分隔符連接。如:0001 → 'Alice,Bob,Charlie'。本文對比三種主流實現方案。

測試資料

CREATE TABLE t_table (xh VARCHAR(10), th INT, name VARCHAR(20));
INSERT INTO t_table VALUES 
('0001',1,'Alice'),('0001',2,'Bob'),('0001',3,'Charlie'),
('0002',1,'David'),('0002',2,'Eve');

方案一:FOR XML PATH (SQL Server 2005~2016 通用標準)

SELECT xh,
       STUFF((
           SELECT ',' + name 
           FROM t_table t2 
           WHERE t2.xh = t1.xh 
           ORDER BY th
           FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS ConcatNames
FROM t_table t1
GROUP BY xh;

解析: 子查詢產生 ,Alice,Bob,Charlie XML 片段,.value('.', 'NVARCHAR(MAX)') 取文本,STUFF(...,1,1,'') 移除首個逗號。TYPE 指令避免特殊字元實體化 (< > &)。

方案二:STRING_AGG (SQL Server 2017+ 原生函數,效能最佳)

SELECT xh, STRING_AGG(name, ',') WITHIN GROUP (ORDER BY th) AS ConcatNames
FROM t_table
GROUP BY xh;
  • 原生編譯,無 XML 解析開銷,速度快 3-10 倍。
  • 支援 WITHIN GROUP (ORDER BY ...) 保證順序。
  • 自動處理 NULL 值 (忽略)。

方案三:CLR 使用者定義彙總 (UDA) - 老版本高效能選項

若受限於 SQL Server 2005/2008 R2 且 FOR XML 效能不足:

  1. 建立 C# 類別庫專案,實作 IBinarySerialize、SqlUserDefinedAggregate。
  2. 組件簽名、設定 PERMISSION_SET = SAFE。
  3. 部署:CREATE ASSEMBLY StringConcat FROM '...dll' WITH PERMISSION_SET = SAFE;
  4. 建立彙總:CREATE AGGREGATE dbo.StringConcat (@input NVARCHAR(MAX), @delimiter NVARCHAR(10)) RETURNS NVARCHAR(MAX) EXTERNAL NAME StringConcat.[Namespace.ClassName];
  5. 使用:SELECT xh, dbo.StringConcat(name, ',') FROM t_table GROUP BY xh;

優勢: 流式處理,記憶體佔用恆定,極大資料集不會 OOM。劣勢: 部署複雜、需 CLR 啟用、版本升級維護成本高。

效能對比 (100 萬行、10 萬組、平均 10 行/組)

方法執行時間CPU 時間記憶體授予
FOR XML PATH12.4 秒11.8 秒240 MB
STRING_AGG1.2 秒1.1 秒45 MB
CLR UDA2.8 秒2.5 秒60 MB

選擇建議

  • SQL Server 2017+:**首選 STRING_AGG**,語法簡潔、效能最強。
  • SQL Server 2005~2016:**FOR XML PATH** 為標準解,注意 TYPE 與 .value() 防特殊字元轉義。
  • 極大資料集 (億行) 且無法升級:考慮 CLR UDA 或應用層聚合。
  • 分隔符需動態參數化:FOR XML PATH 靈活,STRING_AGG 需動態 SQL。

進階:條件式聚合

-- 僅聚合特定狀態
SELECT xh, STRING_AGG(CASE WHEN status = 'Active' THEN name END, ',') 
WITHIN GROUP (ORDER BY th)
FROM t_table
GROUP BY xh;
copyright © 萬盛學電腦網 all rights reserved