指定ORDER BY子句时，为什么SQL Server忽略字符串连接中的值

问题描述

我有下表

Created    Comment
2010/10/10 Text1 
2010/11/11 Text2
2010/12/12 Text3

我需要将所有注释收集到单个字符串

I need gather all comments into the single string

SELECT  @Comment = COALESCE(@Comment, '')
   + CHAR(13) + CHAR(10) + CONVERT(NVARCHAR(30), [dbo].[Comment].[Created], 101) + ': ' + ISNULL([Comment].[Text], '')
    FROM Comment

无需订购即可按预期运行，并返回所有评论。
但运行以下代码后，添加 ORDER BY 子句：

Without ordering it works as expected end return all thee comments.But after running following code where ORDER BY clause is added:

SELECT  @Comment = COALESCE(@Comment, '')
   + CHAR(13) + CHAR(10) + CONVERT(NVARCHAR(30), [Created], 101) + ': ' + ISNULL([Text], '')
    FROM Comment
  ORDER BY Created

仅返回最后一条评论。
是否有人知道为什么ORDER BY会导致串联的奇怪结果？

Return only the last comment.Does any body know why ORDER BY leads to the strange result in concatenation?

PS：如果 FOR XML 子句可以正常工作用于代替串联。

PS: It work fine if FOR XML clause used instead of concatenation Is there a way to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?.

推荐答案

因为您依赖未记录的行为。

Because you are relying on undocumented behaviour.

Microsoft说 。如果计算标量移到计划中的错误位置，则它将停止工作！

Microsoft say "The correct behavior for an aggregate concatenation query is undefined.". If the compute scalar moves to the wrong place in the plan then it just stops working!

这篇关于指定ORDER BY子句时，为什么SQL Server忽略字符串连接中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！