SELECT 赋值与ORDER BY冲突的问题

欢迎您访问我爱IT技术网，今天小编为你分享的编程技术是：【SELECT 赋值与ORDER BY冲突的问题】，下面是详细的分享！

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
 

 

 
　　问题分析：
　　两个处理语句的结果不同，通过查看它们的执行计划应该可以看出原因所在，为此，通过
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顺序，看看结果是否与我的推论相符）
当没有ORDER BY子句时，因为无法确定数据顺序，所以SQL Server必须扫描满足条件的每条数据来得到结果，这样每扫描一条记录都会处理一次，所以结果是我们所预知的
 
　　问题解决方法：
　　修改处理语句，使查询优化器使用与我们需要结果一致的执行方法，可以解决这个问题。
对于示例中的处理语句，可以调整如下:
DECLARE @str nvarchar(4000)
SET @str=N'我不是一个好人，也不是垃圾'
SELECT @str=REPLACE(@str, value, N'<u>' + value + N'</u>')
FROM(
    SELECT TOP 100 PERCENT
        value
    FROM @T
    WHERE CHARINDEX(value, @str) > 0
    ORDER BY CHARINDEX(value, @str) DESC
)A
SELECT @str
 
　　补充：
　　此问题的结论只是笔者对于查询分析的一个推论，并无相应的官方文档可以证明，所以欢迎大家发表自己的看法

以上所分享的是关于SELECT 赋值与ORDER BY冲突的问题，下面是编辑为你推荐的有价值的用户互动：

　　相关问题：向变量赋值的 SELECT 语句不能与数据检索操作结合...

　　答：错误是因为检索的数据列没有全部赋值给变量 1、可以去掉count(RtID)来解决 select top 1 @RtID = RtID from bbsReply 2、可以多加一个变量保存count(RtID) declare @RtID int, @cRtID int select top 1 @RtID = RtID , @cRtID = count(RtID) fro... >>详细

　　相关问题：sql server查询赋值问题

　　答：select top 1 @tempvalue=time from t 或者 set @tempvalue=(select top 1 time from t) >>详细

　　相关问题：关于数据库的问题！我哪里错了？

　　答：看的眼都花了,建议看看系统自带的例子,或者把代码上传,不然怎么检查呀? 字段数组数怎么是25个的,你用了23个,还有连续赋值都赋值到23个了吗? 或者用ACCESS数据库,不要用自带的,以后很纠结的. .版本 2 数据库目录 ＝ 取运行目录 () ＋ “\dataMAC.m... >>详细