|
|
|
|
公众号矩阵

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

简单来说,这样拼接字符串,虽然在语法上支持,但是却不能保证这样的结果正确性,聚合串联查询的行为是不确定的。

作者: 潇湘隐者 来源: DBA闲思杂想录|2021-01-07 09:20

本文转载自微信公众号「DBA闲思杂想录」,作者潇湘隐者。转载本文请联系DBA闲思杂想录公众号。 

在SQL Server中可能有这样的拼接字符串需求,需要将查询出来的一列拼接成字符串,如下案例所示,我们需要将AddressID <=10的AddressLine1字段拼接起来,分隔符为|。如下截图所示。这种方式看起来似乎没有什么问题,而且简单测试也是OK的:

  1. USE AdventureWorks2014; 
  2. GO 
  3. DECLARE @address_list NVARCHAR(MAX); 
  4. SET @address_list =''
  5.  
  6. SELECT @address_list = @address_list + AddressLine1 + '|'  
  7. FROM [Person].[Address] WHERE AddressID <=10; 
  8.  
  9. SELECT @address_list 

但是,如果SQL多了一个排序操作,结果就变了,这个SQL的变量@address_list只获取到了最后一条记录”9833 Mt. Dias Blv.|“,

  1. USE AdventureWorks2014; 
  2. GO 
  3. DECLARE @address_list NVARCHAR(MAX); 
  4. SET @address_list =''
  5.  
  6. SELECT @address_list = @address_list + AddressLine1 + '|'  
  7. FROM [Person].[Address] WHERE AddressID <=10 ORDER BY 1; 
  8.  
  9. SELECT @address_list 

但是你使用其它一些字段排序的话,它又是OK的。在各种实际生产环境中,可能按某个字段排序,字符串拼接就不正常了。但是按有些字段排序又是正常的。有点搞不清套路。下面简单构造一个案例

  1. USE AdventureWorks2014; 
  2. GO 
  3. CREATE TABLE TEST 
  4.     ID  INT NOT NULL 
  5.    ,NAME NVARCHAR(100) NOT NULL  
  6.    ,SortID  INT NOT NULL 
  7.    ,CONSTRAINT PK_TEST PRIMARY KEY (ID) 
  8. ); 
  9.  
  10. INSERT INTO dbo.TEST 
  11. SELECT 1, 'Kerry'  , 1 UNION ALL  
  12. SELECT 2, 'Jerry'  , 2 UNION ALL 
  13. SELECT 3, 'Ken'    , 3 UNION ALL 
  14. SELECT 4, 'Richard', 4 UNION ALL 
  15. SELECT 5, 'Jimmy'  , 5; 
  16.  
  17. DECLARE @name_list NVARCHAR(100); 
  18. SET @name_list=''
  19.  
  20. SELECT @name_list = @name_list + t.NAME + '|' 
  21. FROM dbo.TEST t 
  22. ORDER BY t.SortID; 
  23.  
  24. SELECT @name_list; 

上面脚本测试都正常,下面测试就会出现连接字符串只获取了最后一行记录的情况。

  1. DECLARE @name_list NVARCHAR(100)=''
  2.  
  3. SET @name_list=' ' 
  4. SELECT @name_list = @name_list + t.NAME + '| ' 
  5. FROM dbo.TEST t 
  6. WHERE ID IN (1,2,3) 
  7. ORDER BY t.SortID; 
  8.  
  9. SELECT @name_list; 

在生产环境还有各种魔幻的现象,按其中一个字段排序是正常,换另外一个字段排序就出现这种现象。如果你将上面测试表的字段的大小修改一下,然后测试下面脚本,发现又不会出现这种情况:

  1. USE AdventureWorks2014; 
  2. GO 
  3. DROP TABLE dbo.TEST; 
  4. GO 
  5. CREATE TABLE TEST 
  6.  ID  INT NOT NULL 
  7.    ,NAME NVARCHAR(32) NOT NULL  
  8.    ,SortID  INT NOT NULL 
  9.    ,CONSTRAINT PK_TEST PRIMARY KEY (ID) 
  10. ); 
  11.  
  12. INSERT INTO dbo.TEST 
  13. SELECT 1, 'Kerry'  , 1 UNION ALL  
  14. SELECT 2, 'Jerry'  , 2 UNION ALL 
  15. SELECT 3, 'Ken'    , 3 UNION ALL 
  16. SELECT 4, 'Richard', 4 UNION ALL 
  17. 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:

  1. DECLARE @name_list VARCHAR(512); 
  2.  
  3. SELECT  @name_list= 
  4. SELECT  t.NAME + '|' 
  5. FROM dbo.TEST t 
  6. WHERE ID IN (1,2,3) 
  7. ORDER BY t.SortID 
  8. FOR XML PATH(''), TYPE 
  9. ).value('.''varchar(max)'
  10.  
  11. SELECT @name_list; 

方式2:

  1. SELECT Name + '|' AS 'data()'  
  2. FROM dbo.TEST  
  3. WHERE ID IN (1,2,3) 
  4. FOR XML PATH(''); 

方式3. 借助STUFF函数

方式4. 借助COALESCE函数

注意,使用COALESCE有可能也是不行的。如果定义@name_list为 VARCHAR(512)或VARCHAR(MAX)则是OK的。

  1. DECLARE @name_list VARCHAR(100); 
  2. SELECT @name_list = COALESCE(@name_list + ', ''') + Name  
  3. FROM dbo.TEST 
  4. WHERE ID IN (1,2,3) 
  5. ORDER BY SortID 
  6.  
  7. SELECT @name_list 

5. 使用CRL聚合拼接字符串。

6. 如果SQL Server 2017使用STRING_AGG实现。

  1. SELECT  STRING_AGG(Name'|'AS Departments 
  2. FROM dbo.TEST 
  3. WHERE ID IN (1,2,3) 
  4.  
  5.  
  6. SELECT SortID, STRING_AGG(Name'|'AS Departments 
  7. FROM dbo.TEST 
  8. WHERE ID IN (1,2,3) 
  9. GROUP BY SortID 
  10. 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

【编辑推荐】

  1. IT软硬件平台及操作系统介绍_MySQL数据库学习入门系列教程02
  2. Mysql数据库快速入门与实战精通(从数据库安装到与Java程序数据交互)
  3. 醒哥Java系列-MySQL+JDBC基础到高级
  4. Linux文件目录类命令_MySQL数据库学习入门培训视频课程13
  5. MySQL中的哥哥表、妹妹字段,是什么鬼?
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

数据湖与数据仓库的分析实践攻略

数据湖与数据仓库的分析实践攻略

助力现代化数据管理:数据湖与数据仓库的分析实践攻略
共3章 | 创世达人

1人订阅学习

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

30人订阅学习

数据中心和VPDN网络建设案例

数据中心和VPDN网络建设案例

漫画+案例
共20章 | 捷哥CCIE

209人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微