最近在生產環境中排查一些資料，發現SqlServer用stuff實作行轉列查詢時，效率遠遠低于ORACLE的listagg，查詢1千多條資料，前者需要90秒，后者僅需1秒，這差距有點過分了。我把需求和效果簡化描述如下，希望有方家來出出主意，看看SQLServer有沒有更好的函式或者方法：
1、Oracle下的查詢方法
SELECT heji.xm AS 姓名
,heji.zf AS 總分
,count(*) 科目數
,listagg(kc||cj,'、') WITHIN GROUP(ORDER BY kc) AS 各科分數
FROM (
SELECT xm, SUM(cj) AS zf
FROM (
SELECT '張三' AS xm, '4-物理' AS kc, 93 AS cj FROM dual UNION ALL
SELECT '張三' AS xm, '5-化學' AS kc, 92 AS cj FROM dual UNION ALL
SELECT '張三' AS xm, '6-生物' AS kc, 92 AS cj FROM dual UNION ALL
SELECT '張三' AS xm, '1-語文' AS kc, 95 AS cj FROM dual UNION ALL
SELECT '張三' AS xm, '2-數學' AS kc, 90 AS cj FROM dual UNION ALL
SELECT '張三' AS xm, '3-英語' AS kc, 98 AS cj FROM dual UNION ALL
SELECT '李四' AS xm, '7-歷史' AS kc, 97 AS cj FROM dual UNION ALL
SELECT '李四' AS xm, '8-地理' AS kc, 96 AS cj FROM dual UNION ALL
SELECT '李四' AS xm, '1-語文' AS kc, 90 AS cj FROM dual UNION ALL
SELECT '李四' AS xm, '2-數學' AS kc, 93 AS cj FROM dual UNION ALL
SELECT '李四' AS xm, '3-英語' AS kc, 94 AS cj FROM dual
)
GROUP BY xm
) heji,
(
SELECT '張三' AS xm, '4-物理' AS kc, 93 AS cj FROM dual UNION ALL
SELECT '張三' AS xm, '6-生物' AS kc, 92 AS cj FROM dual UNION ALL
SELECT '張三' AS xm, '5-化學' AS kc, 92 AS cj FROM dual UNION ALL
SELECT '張三' AS xm, '1-語文' AS kc, 95 AS cj FROM dual UNION ALL
SELECT '張三' AS xm, '2-數學' AS kc, 90 AS cj FROM dual UNION ALL
SELECT '張三' AS xm, '3-英語' AS kc, 98 AS cj FROM dual UNION ALL
SELECT '李四' AS xm, '7-歷史' AS kc, 97 AS cj FROM dual UNION ALL
SELECT '李四' AS xm, '8-地理' AS kc, 96 AS cj FROM dual UNION ALL
SELECT '李四' AS xm, '1-語文' AS kc, 90 AS cj FROM dual UNION ALL
SELECT '李四' AS xm, '2-數學' AS kc, 93 AS cj FROM dual UNION ALL
SELECT '李四' AS xm, '3-英語' AS kc, 94 AS cj FROM dual
) feke
WHERE heji.xm = feke.xm
GROUP BY heji.xm, heji.zf
2、我在SQLServer下的查詢方法：
declare @tb table (xm varchar(10), kc varchar(10), cj varchar(10))
insert into @tb (xm, kc, cj)
SELECT '張三' AS xm, '4-物理' AS kc, 93 AS cj UNION ALL
SELECT '張三' AS xm, '5-化學' AS kc, 92 AS cj UNION ALL
SELECT '張三' AS xm, '6-生物' AS kc, 92 AS cj UNION ALL
SELECT '張三' AS xm, '1-語文' AS kc, 95 AS cj UNION ALL
SELECT '張三' AS xm, '2-數學' AS kc, 90 AS cj UNION ALL
SELECT '張三' AS xm, '3-英語' AS kc, 98 AS cj UNION ALL
SELECT '李四' AS xm, '7-歷史' AS kc, 97 AS cj UNION ALL
SELECT '李四' AS xm, '8-地理' AS kc, 96 AS cj UNION ALL
SELECT '李四' AS xm, '1-語文' AS kc, 90 AS cj UNION ALL
SELECT '李四' AS xm, '2-數學' AS kc, 93 AS cj UNION ALL
SELECT '李四' AS xm, '3-英語' AS kc, 94 AS cj
SELECT 姓名 = xm
,總分 = SUM(convert(float,cj))
,科目數 = COUNT(*)
,各科分數 = stuff((select '、' +kc + cj from @tb where xm = a.xm order by kc
for xml path('')
),1,1,''
)
FROM @tb a
GROUP BY xm
兩種查詢方法得到的結果集是一樣的，如下：
寫在最后：我的問題是如何提高SQLServer這類行轉列查詢的效率，不是如何實作行轉列查詢。另外，上述代碼僅僅是輔助描述我的問題，不是問題本身，我的生產環境中，kc列的值是不固定的（不像上面有固定的幾個課程），SQLServer代碼中FROM字句后面的表變數@tb，在生產環境中是一個比較復雜的查詢結果集，而不是一個表。
印象中這是我第一次提問，希望得到解答，以前答題僥幸攢了點分數，這次正好用用。我會盡快來結帖。
uj5u.com熱心網友回復：
準確來說，慢肯定不是因為stuff，這個動作只是字串函式操作，不會特別慢。慢，主要是：
1. stuff內面弄了個子查詢
2. for xml path
3. xm 欄位是否有索引？沒有索引慢不足為奇。
4. @tb 實際情況是一個結果集的話，應該快取到臨時表，然后 臨時表加上 xm 索引。然后對臨時表進行查詢。
如果你用的是 sqlserver2017 或更高版本，可以用新函式 string_agg 來簡化：
SELECT 姓名 = xm
,總分 = SUM(convert(float,cj))
,科目數 = COUNT(*)
,各科分數 = string_agg(kc+cj,'、')
FROM @tb a
GROUP BY xm
從來不覺得 sqlserver 比 orcle 差（同等硬體條件下），只要優化的好，都是一樣的好用。
另外，實在沒辦法， 可以用空間換時間， 做結果報表，觸發生成或定期生成，用戶直接查詢結果表肯定是慢不了的。
比如：學生考試，肯定不是天天考，輸入完成績時立即生成一次報表就是了。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/270008.html
標籤：疑難問題
上一篇：求助帖。
下一篇：一個小教訓