SQL Server解惑——為什么ORDER BY改變了變量的字符串拼接結果
本文轉載自微信公眾號「DBA閑思雜想錄」，作者瀟湘隱者。轉載本文請聯系DBA閑思雜想錄公眾號。
在SQL Server中可能有這樣的拼接字符串需求，需要將查詢出來的一列拼接成字符串，如下案例所示，我們需要將AddressID <=10的AddressLine1字段拼接起來，分隔符為|。如下截圖所示。這種方式看起來似乎沒有什么問題，而且簡單測試也是OK的：
- USE AdventureWorks2014;
- GO
- DECLARE @address_list NVARCHAR(MAX);
- SET @address_list ='';
- SELECT @address_list = @address_list + AddressLine1 + '|'
- FROM [Person].[Address] WHERE AddressID <=10;
- SELECT @address_list
但是,如果SQL多了一個排序操作，結果就變了，這個SQL的變量@address_list只獲取到了最后一條記錄”9833 Mt. Dias Blv.|“，
- USE AdventureWorks2014;
- GO
- DECLARE @address_list NVARCHAR(MAX);
- SET @address_list ='';
- SELECT @address_list = @address_list + AddressLine1 + '|'
- FROM [Person].[Address] WHERE AddressID <=10 ORDER BY 1;
- SELECT @address_list
但是你使用其它一些字段排序的話，它又是OK的。在各種實際生產環境中，可能按某個字段排序，字符串拼接就不正常了。但是按有些字段排序又是正常的。有點搞不清套路。下面簡單構造一個案例
- USE AdventureWorks2014;
- GO
- CREATE TABLE TEST
- (
- ID INT NOT NULL
- ,NAME NVARCHAR(100) NOT NULL
- ,SortID INT NOT NULL
- ,CONSTRAINT PK_TEST PRIMARY KEY (ID)
- );
- INSERT INTO dbo.TEST
- SELECT 1, 'Kerry' , 1 UNION ALL
- SELECT 2, 'Jerry' , 2 UNION ALL
- SELECT 3, 'Ken' , 3 UNION ALL
- SELECT 4, 'Richard', 4 UNION ALL
- SELECT 5, 'Jimmy' , 5;
- DECLARE @name_list NVARCHAR(100);
- SET @name_list='';
- SELECT @name_list = @name_list + t.NAME + '|'
- FROM dbo.TEST t
- ORDER BY t.SortID;
- SELECT @name_list;
上面腳本測試都正常，下面測試就會出現連接字符串只獲取了最后一行記錄的情況。
- DECLARE @name_list NVARCHAR(100)='';
- SET @name_list=' '
- SELECT @name_list = @name_list + t.NAME + '| '
- FROM dbo.TEST t
- WHERE ID IN (1,2,3)
- ORDER BY t.SortID;
- SELECT @name_list;
在生產環境還有各種魔幻的現象，按其中一個字段排序是正常，換另外一個字段排序就出現這種現象。如果你將上面測試表的字段的大小修改一下，然后測試下面腳本，發現又不會出現這種情況：
- USE AdventureWorks2014;
- GO
- DROP TABLE dbo.TEST;
- GO
- CREATE TABLE TEST
- (
- ID INT NOT NULL
- ,NAME NVARCHAR(32) NOT NULL
- ,SortID INT NOT NULL
- ,CONSTRAINT PK_TEST PRIMARY KEY (ID)
- );
- INSERT INTO dbo.TEST
- SELECT 1, 'Kerry' , 1 UNION ALL
- SELECT 2, 'Jerry' , 2 UNION ALL
- SELECT 3, 'Ken' , 3 UNION ALL
- SELECT 4, 'Richard', 4 UNION ALL
- SELECT 5, 'Jimmy' , 5;
初看像一個“Bug”，但是它確實不是一個Bug，官方文檔http://support.microsoft.com/kb/287515有介紹這個現象，但是目前現在這個鏈接失效了，搜索也找不到對應的鏈接了(微軟的官方文檔這一點是相當坑爹，不如Oracle做得好，經常一個鏈接失效，好的情況是鏈接換了，糟糕的情況就是這種，根本找不到了)，下面的資料是在其它資料里面引用KB 287515的內容：
事實證明，此迭代級聯/迭代拼接(iterative concatenation)的功能是不受支持的功能。Microsoft知識庫文章287515指出
You may encounter unexpected results when you apply any operators or expressions to the ORDER BY clause of aggregate concatenation queries.
We do not make any guarantees on the correctness of concatenation queries (like using variable assignments with data retrieval in a specific order). The query output can change in SQL Server 2008 depending on the plan choice, data in the tables etc. You shouldn't rely on this working consistently even though the syntax allows you to write a SELECT statement that mixes ordered rows retrieval with variable assignment.
The correct behavior for an aggregate concatenation query is undefined
簡單來說，這樣拼接字符串，雖然在語法上支持，但是卻不能保證這樣的結果正確性，聚合串聯查詢的行為是不確定的。如果想安全可靠的拼接字符串的話，有下面一些方式：
- 使用游標循環循環處理拼接字符串。
- 使用XML查詢拼接字符串
方式1：
- DECLARE @name_list VARCHAR(512);
- SELECT @name_list=
- (
- SELECT t.NAME + '|'
- FROM dbo.TEST t
- WHERE ID IN (1,2,3)
- ORDER BY t.SortID
- FOR XML PATH(''), TYPE
- ).value('.', 'varchar(max)')
- SELECT @name_list;
方式2：
- SELECT Name + '|' AS 'data()'
- FROM dbo.TEST
- WHERE ID IN (1,2,3)
- FOR XML PATH('');
方式3. 借助STUFF函數
方式4. 借助COALESCE函數
注意，使用COALESCE有可能也是不行的。如果定義@name_list為 VARCHAR(512)或VARCHAR(MAX)則是OK的。
- DECLARE @name_list VARCHAR(100);
- SELECT @name_list = COALESCE(@name_list + ', ', '') + Name
- FROM dbo.TEST
- WHERE ID IN (1,2,3)
- ORDER BY SortID
- SELECT @name_list
5. 使用CRL聚合拼接字符串。
6. 如果SQL Server 2017使用STRING_AGG實現。
- SELECT STRING_AGG(Name, '|') AS Departments
- FROM dbo.TEST
- WHERE ID IN (1,2,3)
- SELECT SortID, STRING_AGG(Name, '|') AS Departments
- FROM dbo.TEST
- WHERE ID IN (1,2,3)
- GROUP BY SortID
- ORDER BY SortID;
參考資料：
https://stackoverflow.com/questions/5538187/why-sql-server-ignores-vaules-in-string-concatenation-when-order-by-clause-speci/5538210#5538210
https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv
