MSSQL 透過 Select 指定變數串接欄位值遇到 Order By 的問題 (Multi-Row Variable Assignment and ORDER BY)

前言

有同事詢問 使用 Select 變數將某個欄位值以逗號串起來時，如果最後加上 Order By 時，
變數值卻只會有一筆，而不會將所有資料欄位值串起來?

練習

資料準備

-- drop table #t1
create table #t1
(d1 date)
go

insert into #t1(d1) 
values('2017/1/1')
, ('2018/3/3')
, ('2017/12/1')
, ('2019/3/3')
, ('2018/12/1');

執行測試

DECLARE @yearStr VARCHAR(MAX)
SET @yearStr = ''
-- 找出目前有的年度(拼接字串) 沒加 Order
SELECT @yearStr = @yearStr + QUOTENAME(YEAR(d1)) + N',' 
FROM #t1
--ORDER BY YEAR(d1) 
-- 執行結果 [2017],[2018],[2017],[2019],[2018],
PRINT(@yearStr)

SET @yearStr = ''
-- 找出目前有的年度(拼接字串) , 加上 Order By QUOTENAME(YEAR(d1))
SELECT @yearStr = @yearStr + QUOTENAME(YEAR(d1)) + N',' 
FROM #t1
ORDER BY QUOTENAME(YEAR(d1))
-- 執行結果 [2019],
PRINT(@yearStr)

從上面可以發現，有加 Order By QUOTENAME(YEAR(d1)) 的變數值只有最後一筆的資料，
這是怎麼回事呢?

查看執行計畫

我們開啟實際的執行計畫後，再來執行上面的SQL，來看看到是底是什麼原因呢?

SET SHOWPLAN_TEXT ON;

先執行 SET SHOWPLAN_TEXT ON; 後，再執行上面的 SQL

從上圖的執行計畫可以發現，有 Order By 的執行計畫，
第一個是 Order By 之後才是 指定變數串接欄位值，
也就是因為這樣導致最後變數值只會是最後一個資料的值。

如果我們將 Order By QUOTENAME(YEAR(d1)) 改成 Order By d1 ，

SET SHOWPLAN_TEXT OFF;

開啟 包含實際執行計畫 來看一下結果，

DECLARE @yearStr VARCHAR(MAX)
SET @yearStr = ''
-- 找出目前有的年度(拼接字串) 
SELECT @yearStr = @yearStr + QUOTENAME(YEAR(d1)) + N',' 
FROM #t1
ORDER BY d1;
-- 輸出為 [2017],[2017],[2018],[2018],[2019],
PRINT(@yearStr);

當然如果資料表又許多 Index 的影響之下，在 Select 變數加上 Order By 時，
有時就會有上述我們提到的問題，那怎麼辦呢?

解法

1.不要使用 Order By

在 SQL Script 中， Order by 也是蠻吃效能的，所以可以不用就儘量不要用

2.使用前先檢查執行計畫

就像我們上面透過執行計畫來看看，Order By 是否會跑到了 Select 變數之前

3.使用 FOR XML PATH

如果真的要使用 Order By 的話，比較穩的方式是使用 For XML Path 的方式，如下，

DECLARE @yearStr VARCHAR(MAX)
SELECT  
@yearStr = STUFF( 
(
	SELECT ', ' + QUOTENAME(YEAR(d1)) 
	FROM #t1 
	ORDER BY QUOTENAME(YEAR(d1)) 
	FOR XML PATH('')
), 1, 1, '');
PRINT(@yearStr);

參考資料

將某個欄位值以逗號串起來
Multi-Row Variable Assignment and ORDER BY
將多筆資料中某一欄的資料轉作一列顯示