使用 select 语句，轮询表中的数据，并且处理变量数据时，如果有order by语句，则得不到想要的结果，但去掉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)) |
2 | |
|
|
|
|
|
|-- |
table scan(object:(@t)) |
1 |
从上面的列表可以看出，两种处理的最大差异，在于赋值前，是否有order by 子句，从一般的理解上，可能会认为是否排序并不重要，但换个角度来看问题，就比较容易理解为什么有order by子句后得不到我们想要的结果了：
当有order by子句时，对于select @str = 这种赋值处理，sql server认为赋值处理肯定只会保留最后一条记录的处理结果，而order by子句确定了数据顺序，也就知道最后一条记录是那个，因此只会处理order by的最后一条记录。（读者可以自行去测试一下，调整order by顺序，看看结果是否与我的推论相符）