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

似乎这是一个 known issue with Aggregate Concatenation Queries .

从链接:

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

可以通过使用计算列来完成此操作,例如:

DROP TABLE dbo.temp;

CREATE TABLE dbo.temp
(
  s varchar(20)
 ,t AS REVERSE(s)
);
INSERT INTO dbo.temp (s) VALUES ('foo');
INSERT INTO dbo.temp (s) VALUES ('bar');
INSERT INTO dbo.temp (s) VALUES ('baz');
INSERT INTO dbo.temp (s) VALUES ('blah');
GO

-- Using the function directly doesn't work:
DECLARE @s varchar(2000);
SELECT s, REVERSE(s) FROM dbo.temp ORDER BY REVERSE(s);
SET @s = '';
SELECT @s = @s + s FROM dbo.temp ORDER BY REVERSE(s);
SELECT @s;
GO

-- Hiding the function in a computed column works:
DECLARE @s varchar(2000);
SELECT s, t FROM dbo.temp ORDER BY t;
SET @s = '';
SELECT @s = @s + s FROM dbo.temp ORDER BY t;
SELECT @s;
GO

我不知道这是否有帮助,但当我尝试以下方法时:

set @s = '          ';

select @s = @s + s from #test order by dbo.wtfunc(s);
select @s AS myTest

我得到这个(注意在“foo”前面有空格,后面没有空格):

  foo

我想这是个不起眼的小虫子吧?!