在SQL Server中可能有這樣的拼接字串需求，需要將查詢出來的一列拼接成字串，如下案例所示，我們需要將AddressID <=10的AddressLine1拼接起來，分隔符為|，如下截圖所示，這種方式看起來似乎沒有什么問題，而且簡單測驗也是OK：
USE AdventureWorks2014;GODECLARE @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;GODECLARE @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;GOCREATE TABLE TEST
(
ID INT NOT NULL
,NAME NVARCHAR(100) NOT NULL
,SortID INT NOT NULL
,CONSTRAINT PK_TEST PRIMARY KEY (ID)
);
INSERT INTO dbo.TESTSELECT 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 tORDER BY t.SortID;
SELECT @name_list;
上面腳本測驗都正常，下面測驗就會出現連接字串只獲取了最后一行記錄的情況，
DECLARE @name_list NVARCHAR(100)='';
SET @name_list=' '
SELECT @name_list = @name_list + t.NAME + '| '
FROM dbo.TEST tWHERE ID IN (1,2,3)
ORDER BY t.SortID;
SELECT @name_list;
在生產環境還有各種魔幻的現象，按其中一個欄位排序是正常，換另外一個欄位排序就出現這種現象，如果你將上面測驗表的欄位的大小修改一下，然后測驗下面腳本，發現又不會出現這種情況：
USE AdventureWorks2014;GODROP TABLE dbo.TEST;
GOCREATE TABLE TEST
(
ID INT NOT NULL
,NAME NVARCHAR(32) NOT NULL
,SortID INT NOT NULL
,CONSTRAINT PK_TEST PRIMARY KEY (ID)
);
INSERT INTO dbo.TESTSELECT 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
簡單來說，這樣拼接字串，雖然在語法上支持，但是卻不能保證這樣的結果正確性，聚合串聯查詢的行為是不確定的，如果想安全可靠的拼接字串的話，有下面一些方式：
1： 使用游標回圈回圈處理拼接字串，
2： 使用XML查詢拼接字串
方式1：
DECLARE @name_list VARCHAR(512);
SELECT @name_list=(
SELECT t.NAME + '|'
FROM dbo.TEST tWHERE 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.TESTWHERE 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.TESTWHERE ID IN (1,2,3)
SELECT SortID, STRING_AGG(Name, '|') AS Departments
FROM dbo.TESTWHERE 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/246055.html
標籤：其他