2021-01-07

博客园Logo
首页
新闻
博问
专区
闪存
班级
代码改变世界
搜索
注册
登录
Posts - 1091, Articles - 0, Comments - 1855 Cnblogs Dashboard Login
HOMECONTACTGALLERYSUBSCRIBE
RSS
潇湘隐者
SQL Server解惑——为什么ORDER BY改变了变量的字符串拼接结果
2021-01-07 08:44 潇湘隐者 阅读(102) 评论(0) 编辑 收藏

在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

clip_image001

但是,如果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

clip_image002

但是你使用其它一些字段排序的话，它又是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;

clip_image003

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

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;

image

初看像一个“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 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

clip_image004

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

作者：潇湘隐者
出处：http://www.cnblogs.com/kerrycode/

如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨小小打赏一下吧，如果囊中羞涩，不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！

本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
好文要顶 关注我 收藏该文
潇湘隐者
关注 - 175
粉丝 - 2890
推荐博客
+加关注
20
« 上一篇： SQL Server脚本分享:get_database_detail_info.sql
分类 数据库技术(MS SQL)
标签 Sql Server , Order By , 拼接字符串
刷新评论刷新页面返回顶部
登录后才能发表评论，立即 登录 或 注册， 访问 网站首页
【推荐】News: 大型组态、工控、仿真、CADGIS 50万行VC++源码免费下载
【推荐】有你助力，更好为你——博客园用户消费观调查，附带小惊喜！
【推荐】AWS携手博客园为开发者送福利，注册立享12个月免费套餐
【推荐】七牛云新老用户同享 1 分钱抢 CDN 1TB流量大礼包！
【推荐】了不起的开发者，挡不住的华为，园子里的品牌专区
【推荐】未知数的距离，毫秒间的传递，声网与你实时互动
【推荐】新一代 NoSQL 数据库，Aerospike专区新鲜入驻

相关博文：
· sqlserver查询（SELECT，where，distinct，like查询，in，isnull，groupby和having，orderby，as）
· sql语句中orderby的用法
· orderby多个条件
· MySQLORDERBYIF()条件排序
· mysqlorderbylimit的一个坑
» 更多推荐…

最新 IT 新闻:
· 互联网新巨头二号位简史
· BAT移动生态战争2021：对生态的研究和发力成为重中之重
· 《绝地求生》续作或正在开发 登陆PC/主机/移动端
· 特斯拉与BBA，必有一战
· 一线｜瑞幸郭谨一回应被“逼宫”：举报信是陆正耀等起草，当事员工不知情
» 更多新闻…
历史上的今天：
2016-01-07 Windows Server 2012 Recycle Bin corrupted
2015-01-07 Write on ……… failed: 112(failed to retrieve text for this error. Reason: 15105)
About
个人简介：网名潇湘隐者/潇湘剑客、英文名Kerry，兴趣广泛，广泛涉猎，个性随意。执意做一名会写代码的DBA，混迹于IT行业

个人微信公众号: 「DBA闲思杂想录」 欢迎关注！

博客简介： 这里是潇湘隐者的一亩三分地，偶尔意兴阑珊的时候，整理打理下本“菜园”，本“菜园”主要关注各类数据库技术（ORACLE、MS SQL， MySQL，Hbase…）以及NOSQL、NET、JAVA等各类技术。在这里，记录着我成长、学习、工作过程的点点滴滴（笔记、总结、研究、问题解决…），本人对数据库管理、数据库开发、性能优化、数据挖掘、BI等有着浓厚的兴趣，欢迎大家一起探讨、研究各种技术话题！非诚勿扰。

联系方式： 邮箱地址：kerry2008code@qq.com

DBA—技术交流分享群: 335155934

DBA—技术交流分享群2: 294770674

加群请回答下面问题中任何一个： ORACLE ： SGA由哪几部分组成？ SQL SERVER: SQL锁类型有那些？

座右铭 ：命在于自造，运在于自改，福在于自求，境在于自选，友在于自择，习在于自改，悟在于自通，凡事在于自己。人生就是一场修行！走自己的道，修自己的行。脚踏实地、切忌浮躁，眼界决定高度，思路决定出路

年轻时的付出，都会是一种沉淀，它们会默默铺路，只为让你成为更好的人。心简单，世界就简单，幸福才会生长；心自由，生活就自由，到哪都有快乐.回忆中，总有些瞬间，能温暖整个曾经…

昵称： 潇湘隐者
园龄： 11年6个月
荣誉： 推荐博客
粉丝： 2890
关注： 175
+加关注
提交
最新随笔
SQL Server解惑——为什么ORDER BY改变了变量的字符串拼接结果
SQL Server脚本分享:get_database_detail_info.sql
ashtop学习分享
DBA思考系列——学会接受平凡的自己！
SQL Server中datetimeset转换datetime类型问题浅析
MySQL如何计算统计redo log大小
个人博客的简单通告
SQL Server如何自动化修改数据库的physical_name
MySQL慢查询日志出现commit释疑
[翻译]——MySQL Server Variable: sync_binlog (Doc ID 1501926.1)
最新评论
Re:Linux如何查找大文件或目录总结
我偷偷的点了反对！0.5956509666663876

– zzl_666
Re:DBA思考系列——学会接受平凡的自己！
@MSSQL123 人到中年，随着阅历和经历的变化，总会有不同的感悟。了解生活的残酷 依然热爱生活，同勉！… – 潇湘隐者
Re:DBA思考系列——学会接受平凡的自己！
同感，人到中年，只能负重前行，我想着是所有中年人无奈而又必须接受的事实

– MSSQL123
Re:MySQL中lock tables和unlock tables浅析
LOCAL修饰符表示可以允许在其他会话中对在当前会话中获取了READ锁的的表执行插入。
——这句话卧槽。。。。。实在理解不了，想吐血！

– mystery-V
Re:表驱动方法
@关忆北 厉害，这个细节都注意到了！… – 潇湘隐者
随笔档案
2021年1月(3)
2020年12月(12)
2020年11月(7)
2020年10月(5)
2020年9月(6)
2020年8月(9)
2020年7月(10)
2020年6月(10)
2020年5月(9)
2020年4月(4)
2020年3月(7)
2020年2月(6)
2020年1月(1)
2019年12月(3)
2019年11月(7)
2019年10月(7)
2019年9月(12)
2019年8月(14)
2019年7月(8)
2019年6月(10)
更多
积分与排名
积分 - 2294715
排名 - 35
推荐排行榜

1. 你人生中的那口井挖了没有？(131)
2. SQL Server 中WITH (NOLOCK)浅析(115)
3. ORACLE基本数据类型总结(58)
4. 深入理解Linux修改hostname(56)
5. MS SQL 日常维护管理常用脚本(二)(56)
   日历
   < 2021年1月 >
   日 一 二 三 四 五 六
   27 28 29 30 31 1 2
   3 4 5 6 7 8 9
   10 11 12 13 14 15 16
   17 18 19 20 21 22 23
   24 25 26 27 28 29 30
   31 1 2 3 4 5 6
   随笔分类
   .NET技术(10)
   DBA思考系列(3)
   NoSQL(Neo4j)(1)
   Python学习笔记(13)
   Unix&Linux技术(212)
   Window 技术汇集(16)
   Zabbix监控(20)
   编程技巧(3)
   存储网络(5)
   工欲善其事必先利其器(17)
   其它技巧(1)
   软件重构(4)
   数据库技术(Azure)(3)
   数据库技术(MongoDB)(1)
   数据库技术(MS SQL)(341)
   数据库技术(My SQL)(109)
   数据库技术(Oracle)(318)
   数据库技术(PostgreSQL)(1)
   数据库建模(1)
   数据库重构(2)
   更多
   阅读排行榜
6. Linux 查看服务器开放的端口号(227557)
7. 深入理解Linux修改hostname(213112)
8. Linux如何搜索查找文件里面内容(192666)
9. ORACLE VARCHAR2最大长度问题(185620)
10. ORACLE基本数据类型总结(184887)
11. ORA-12514, TNS:listener does not currently know of service requested in connect descriptor案例2(178344)
12. MySQL ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)的真正原因(167381)
13. Crontab定时任务配置(150977)
14. Linux如何查找大文件或目录总结(148751)
15. Linux mysql 5.6： ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)(144266)
16. Linux的NTP配置总结(143779)
17. Linux如何查看JDK的安装路径(142300)
18. IndentationError: unindent does not match any outer indentation level笔记(140465)
19. Linux平台卸载MySQL总结(139433)
20. Linux查看系统开机时间(135077)