SELECT賦值與ORDERBY沖突的問題

　　問題描述:

　　使用 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

　　 

　　補充

　　此問題的結論只是筆者對於查詢分析的一個推論並無相應的官方文檔可以證明所以歡迎大家發表自己的看法

 
From:http://tw.wingwit.com/Article/program/Oracle/201311/17560.html