SELECT 赋值与ORDER BY冲突的问题

问题描述:

使用 SELECT 语句，轮询表中的数据，并且处理变量数据时，如果有ORDER BY语句，则得不到想要的结果，但去掉ORDER BY，结果正常。

具体的问题表现参考下面的问题重现代码

问题重现代码

-- 测试数据

DECLARE @T TABLE(id int,value nvarchar(16))

INSERT INTO @T SELECT

1, N'好人' UNION ALL SELECT

2, N'坏人' UNION ALL SELECT

3, N'吃饭' UNION ALL SELECT

4, N'垃圾'

-- 赋值处理

DECLARE @str nvarchar(4000)

SET @str = N'我不是一个好人，也不是垃圾'

SELECT @str = REPLACE(@str, value, N'<u>' + value + N'</u>')

FROM @T

WHERE CHARINDEX(value, @str) > 0

--ORDER BY CHARINDEX(value, @str) DESC

SELECT @str

/* -- 结果(当赋值处理语句注释掉ORDER BY 时)

我不是一个<u>好人</u>，也不是<u>垃圾</u>

-- */

/* -- 结果(当赋值处理语句加上ORDER BY 时)

我不是一个<u>好人</u>，也不是垃圾

-- */

问题分析：

两个处理语句的结果不同，通过查看它们的执行计划应该可以看出原因所在，为此，通过

SET SHOWPLAN_ALL ON

输出了两种执行语句的执行计划（仅StmtText部分，有兴趣的读者在自己的电脑上测试的时候，可以去了解其他部分的信息）

StmtText						Step
DECLARE @str nvarchar(4000) SET @str = N'我不是一个好人，也不是垃圾'
SELECT @str = REPLACE(@str, value, N'<u>' + value + N'</u>') FROM @T WHERE CHARINDEX(value, @str) > 0						4
	|--	Compute Scalar(DEFINE:([Expr1002]=replace([@str], @T.[value], '<u>'+@T.[value]+'</u>')))				3
		|--	Filter(WHERE:(charindex(@T.[value], [@str], NULL)>0))			2
			|--	Table Scan(OBJECT:(@T))		1
DECLARE @str nvarchar(4000) SET @str = N'我不是一个好人，也不是垃圾'
SELECT @str = REPLACE(@str, value, N'<u>' + value + N'</u>') FROM @T WHERE CHARINDEX(value, @str) > 0 ORDER BY CHARINDEX(value, @str) DESC						5
	|--	Sort(ORDER BY:([Expr1003] DESC))				4
		|--	Compute Scalar(DEFINE:([Expr1002]=replace([@str], @T.[value], '<u>'+@T.[value]+'</u>'), [Expr1003]=charindex(@T.[value], [@str], NULL)))			3
			|--	Filter(WHERE:(charindex(@T.[value], [@str], NULL)>0))