问题描述: 使用 SELECT 语句轮询表中的数据并且处理变量数据时如果有ORDER BY语句则得不到想要的结果但去掉ORDER BY结果正常 具体的问题表现参考下面的问题重现代码
问题重现代码 测试数据 DECLARE @T TABLE(id intvalue nvarchar()) INSERT INTO @T SELECT N好人 UNION ALL SELECT N坏人 UNION ALL SELECT N吃饭 UNION ALL SELECT N垃圾
赋值处理 DECLARE @str nvarchar() SET @str = N我不是一个好人也不是垃圾 SELECT @str = REPLACE(@str value N<u> + value + N</u>) FROM @T WHERE CHARINDEX(value @str) > 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() SET @str = N我不是一个好人也不是垃圾
SELECT @str = REPLACE(@str value N<u> + value + N</u>) FROM @T WHERE CHARINDEX(value @str) >
| Compute Scalar(DEFINE:([Expr]=replace([@str] @T[value] <u>+@T[value]+</u>)))
| Filter(WHERE:(charindex(@T[value] [@str] NULL)>))
| Table Scan(OBJECT:(@T))
DECLARE @str nvarchar() SET @str = N我不是一个好人也不是垃圾
SELECT @str = REPLACE(@str value N<u> + value + N</u>) FROM @T WHERE CHARINDEX(value @str) > ORDER BY CHARINDEX(value @str) DESC
| Sort(ORDER BY:([Expr] DESC))
| Compute Scalar(DEFINE:([Expr]=replace([@str] @T[value] <u>+@T[value]+</u>) [Expr]=charindex(@T[value] [@str] NULL)))
| Filter(WHERE:(charindex(@T[value] [@str] NULL)>))
| Table Scan(OBJECT:(@T))
从上面的列表可以看出两种处理的最大差异在于赋值前是否有ORDER BY 子句从一般的理解上可能会认为是否排序并不重要但换个角度来看问题就比较容易理解为什么有ORDER BY子句后得不到我们想要的结果了 当有ORDER BY子句时对于SELECT @str = 这种赋值处理SQL Server认为赋值处理肯定只会保留最后一条记录的处理结果而ORDER BY子句确定了数据顺序也就知道最后一条记录是那个因此只会处理ORDER BY的最后一条记录（读者可以自行去测试一下调整ORDER BY顺序看看结果是否与我的推论相符） 当没有ORDER BY子句时因为无法确定数据顺序所以SQL Server必须扫描满足条件的每条数据来得到结果这样每扫描一条记录都会处理一次所以结果是我们所预知的
问题解决方法 修改处理语句使查询优化器使用与我们需要结果一致的执行方法可以解决这个问题 对于示例中的处理语句可以调整如下: DECLARE @str nvarchar() SET @str = N我不是一个好人也不是垃圾 SELECT @str = REPLACE(@str value N<u> + value + N</u>) FROM( SELECT TOP PERCENT value FROM @T WHERE CHARINDEX(value @str) > ORDER BY CHARINDEX(value @str) DESC )A SELECT @str
补充 此问题的结论只是笔者对于查询分析的一个推论并无相应的官方文档可以证明所以欢迎大家发表自己的看法 |
