[[374304]]

本文轉(zhuǎn)載自微信公眾號(hào)「DBA閑思雜想錄」，作者瀟湘隱者。轉(zhuǎn)載本文請(qǐng)聯(lián)系DBA閑思雜想錄公眾號(hào)。   

在SQL Server中可能有這樣的拼接字符串需求，需要將查詢出來(lái)的一列拼接成字符串，如下案例所示，我們需要將AddressID <=10的AddressLine1字段拼接起來(lái)，分隔符為|。如下截圖所示。這種方式看起來(lái)似乎沒(méi)有什么問(wèn)題，而且簡(jiǎn)單測(cè)試也是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多了一個(gè)排序操作，結(jié)果就變了，這個(gè)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的。在各種實(shí)際生產(chǎn)環(huán)境中，可能按某個(gè)字段排序，字符串拼接就不正常了。但是按有些字段排序又是正常的。有點(diǎn)搞不清套路。下面簡(jiǎn)單構(gòu)造一個(gè)案例

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; 

上面腳本測(cè)試都正常，下面測(cè)試就會(huì)出現(xiàn)連接字符串只獲取了最后一行記錄的情況。

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; 

在生產(chǎn)環(huán)境還有各種魔幻的現(xiàn)象，按其中一個(gè)字段排序是正常，換另外一個(gè)字段排序就出現(xiàn)這種現(xiàn)象。如果你將上面測(cè)試表的字段的大小修改一下，然后測(cè)試下面腳本，發(fā)現(xiàn)又不會(huì)出現(xiàn)這種情況：

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; 

初看像一個(gè)“Bug”，但是它確實(shí)不是一個(gè)Bug，官方文檔http://support.microsoft.com/kb/287515有介紹這個(gè)現(xiàn)象，但是目前現(xiàn)在這個(gè)鏈接失效了，搜索也找不到對(duì)應(yīng)的鏈接了(微軟的官方文檔這一點(diǎn)是相當(dāng)坑爹，不如Oracle做得好，經(jīng)常一個(gè)鏈接失效，好的情況是鏈接換了，糟糕的情況就是這種，根本找不到了)，下面的資料是在其它資料里面引用KB 287515的內(nèi)容：

事實(shí)證明，此迭代級(jí)聯(lián)/迭代拼接(iterative concatenation)的功能是不受支持的功能。Microsoft知識(shí)庫(kù)文章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

簡(jiǎn)單來(lái)說(shuō)，這樣拼接字符串，雖然在語(yǔ)法上支持，但是卻不能保證這樣的結(jié)果正確性，聚合串聯(lián)查詢的行為是不確定的。如果想安全可靠的拼接字符串的話，有下面一些方式：

1. 使用游標(biāo)循環(huán)循環(huán)處理拼接字符串。
2. 使用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函數(shù)

方式4. 借助COALESCE函數(shù)

注意，使用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實(shí)現(xiàn)。

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