SQL Server解惑——为什么ORDER BY改变了变量的字符串拼接结果

SQL Server解惑——为什么ORDER BY改变了变量的字符串拼接结果

在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;

事实证明，此迭代级联/迭代拼接(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;

参考资料：