带有select和order by的tsql字符串concat不适用于order by子句中的函数?

问题描述

考虑下面的 tsql...

Consider the following tsql...

create function dbo.wtfunc(@s varchar(50)) returns varchar(10) begin return left(@s, 2); end
GO
select t.* into #test from (
    select 'blah' as s union
    select 'foo' union
    select 'bar'
) t
select * from #test;

declare @s varchar(100); 
set @s = '';

select @s = @s + s from #test order by s;
select @s;
set @s = '';
select @s = @s + s from #test order by dbo.wtfunc(s);
select @s;
/* 2005 only*/
select cast((select s+'' from #test order by dbo.wtfunc(s) for xml path('')) as varchar(100))

drop function dbo.wtfunc;
drop table #test;

我已经在 mssql 2000 和 2005 上尝试过，当按顺序使用函数时，两者都没有连接字符串.在 2005 年，for xml path('') 确实有效.输出是...

I've tried it on mssql 2000 and 2005 and both do not concat the string when using a function in the order by. On 2005, the for xml path('') does work. The output is...

bar
blah
foo

barblahfoo

foo --nothing concatenated?

barblahfoo

我找不到记录的地方.有人可以解释为什么这不起作用吗?

I can't find where this is documented. Can someone shed some light on why this doesn't work?

这是实际的执行计划.显然排序和计算标量的顺序不同...

Here are the actual execution plans. Obviously the sort and compute scalar are not in the same order...

推荐答案

这似乎是一个 聚合串联查询的已知问题.

来自链接:

ANSI SQL-92 规范要求 ORDER BY 子句引用的任何列与结果集匹配，该结果集由 SELECT 列表中的列定义.当表达式应用于 ORDER BY 的成员时子句，结果列未在 SELECT 列表中公开，从而导致未定义的行为."

这篇关于带有select和order by的tsql字符串concat不适用于order by子句中的函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！