萬盛學電腦網

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

SQL Server 跨欄位彙總技巧:用 XML + XQuery 實現單列 MIN/MAX/SUM/AVG

標準彙總函數 (MIN/MAX/SUM/AVG) 僅支援「同一欄位多列」運算。若需對「同一列多欄位」做彙總(如一筆資料的 6 個數值欄位求最大值),常見做法是 CASE WHEN 或 UNPIVOT,但欄位多時寫法冗長。本文介紹利用 FOR XML PATH 將列轉為 XML,再用 XQuery 還原為資料列集合,最後套用標準彙總函數的巧技。

測試環境建立

DECLARE @t TABLE (
    id SMALLINT,
    a SMALLINT, b SMALLINT, c SMALLINT,
    d SMALLINT, e SMALLINT, f SMALLINT
);
INSERT INTO @t SELECT 1, 1, 2, 3, 4, 6, 7 UNION ALL
SELECT 2, 34, 45, 56, 54, 9, 6;

核心查詢

SELECT a.*, c.*
FROM @t a
OUTER APPLY (
    SELECT doc = (
        SELECT * FROM @t AS doc WHERE id = a.id FOR XML PATH(''), TYPE
    )
) b
OUTER APPLY (
    SELECT 
        MIN(r) AS minValue,
        MAX(r) AS maxValue,
        SUM(r) AS sumValue,
        AVG(r) AS avgValue
    FROM (
        SELECT CAST(CAST(d.n.query('text()') AS VARCHAR(MAX)) AS INT) AS r
        FROM doc.nodes('/a,b,c,d,e,f') D(n)
    ) tt
) c;

執行結果

idabcdefminValuemaxValuesumValueavgValue
112346717233
2344556549665620434

原理解析

  1. FOR XML PATH(''), TYPE:將符合 id 的單列轉為無根元素的 XML 片段,型別為 xml。
  2. doc.nodes('/a,b,c,d,e,f'):XQuery 針對每個子元素 (<a>, <b>...) 產生一列。
  3. query('text()') 取出文字內容 → 轉 VARCHAR → 轉 INT 得到純量值 r。
  4. 外層套用 MIN/MAX/SUM/AVG 完成跨欄彙總。

優缺點與適用場景

  • ✅ 寫法固定、欄位增減只需調整 XQuery 路徑、避免動態 SQL。
  • ⚠️ XML 序列化/解析有 CPU 開銷,百萬列以上效能不如 UNPIVOT 或應用層處理。
  • 🎯 適合:報表即席查詢、欄位固定但數量中等 (10~50)、不想寫冗長 CASE 的情境。
copyright © 萬盛學電腦網 all rights reserved