萬盛學電腦網

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

SQL Server 相同 ID 多行合併:XML PATH、STRING_AGG 與自定義函數三種實現

將相同分組鍵的多行字串值合併為單行 (逗號分隔/自定義分隔符) 是報表開發常見需求。本文對比三種主流實現:`FOR XML PATH` (通用)、`STRING_AGG` (2017+ 原生)、自定義標量函數 (舊版相容),並給效能建議。

示例資料

CREATE TABLE #Demo (Code VARCHAR(10), Txt NVARCHAR(50));
INSERT #Demo VALUES 
('102', N'科學'), ('102', N'數學'), ('102', N'數學1'),
('101', N'美術'), ('101', N'體育'), ('101', N'體育1');

方法 1:FOR XML PATH (SQL Server 2005+ 通用)

SELECT 
    Code,
    STUFF((
        SELECT ',' + Txt 
        FROM #Demo d2 
        WHERE d2.Code = d1.Code 
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS MergedTxt
FROM #Demo d1
GROUP BY Code;

原理:子查詢生成 `值1值2...` → `.value('.', 'NVARCHAR(MAX)')` 串聯 → `STUFF(...,1,1,'')` 移除首個分隔符。

方法 2:STRING_AGG (SQL Server 2017+ / Azure SQL 原生,效能最佳)

SELECT Code, STRING_AGG(Txt, ',') WITHIN GROUP (ORDER BY Txt) AS MergedTxt
FROM #Demo
GROUP BY Code;
  • 單次掃描、無 XML 解析開銷、支援 `WITHIN GROUP (ORDER BY ...)` 保證順序
  • 大資料量 (百萬行) 效能優於 XML PATH 10 倍以上

方法 3:自定義標量函數 (教學/舊版相容,效能差)

CREATE FUNCTION dbo.ConcatByCode (@Code VARCHAR(20))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX) = N'';
    SELECT @Result += ',' + Txt FROM #Demo WHERE Code = @Code;
    RETURN STUFF(@Result, 1, 1, '');
END

SELECT Code, dbo.ConcatByCode(Code) AS MergedTxt
FROM #Demo
GROUP BY Code;

**缺點**:逐行執行函數 (RBAR)、無法平行化、計畫快取膨脹,**生產環境大表嚴禁使用**。

效能對比 (100 萬行測試)

方法耗時CPU邏輯讀取平行度
FOR XML PATH~2.3 秒高高單執行緒
STRING_AGG~0.18 秒低低支援平行
標量函數~45 秒極高極高單執行緒

臨時表補充:局部 (#) vs 全域 (##)

  • `#LocalTemp`:僅當前 Session 可見,Session 結束自動清理
  • `##GlobalTemp`:所有 Session 可見,建立者 Session 結束且無其他引用時自動清理
  • 跨程序/觸發器共享資料用 `##`,單程序內部暫存用 `#`

進階:分組連接帶排序、去重、長度截斷

-- STRING_AGG 進階
SELECT Code, 
       STRING_AGG(DISTINCT Txt, '; ') WITHIN GROUP (ORDER BY Txt) AS Merged
FROM #Demo
GROUP BY Code;

-- XML PATH 進階 (去重 + 排序 + 長度限制 4000)
SELECT Code,
       LEFT(STUFF((
           SELECT '; ' + Txt 
           FROM (SELECT DISTINCT Txt FROM #Demo d2 WHERE d2.Code = d1.Code) d3
           ORDER BY Txt FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)'), 1, 2, ''), 4000) AS Merged
FROM #Demo d1
GROUP BY Code;

**選型建議**:2017+ 必用 `STRING_AGG`;2005-2016 用 `FOR XML PATH` 並加適當索引 (`Code, Txt`);避免標量函數處理集合操作。

copyright © 萬盛學電腦網 all rights reserved