SELECT 賦值與ORDER BY衝突的問題

來源:互聯網
上載者:User

  問題描述:
  使用 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   WHERECHARINDEX(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   WHERECHARINDEX(value, @str)> 0   ORDERBYCHARINDEX(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
 
  補充:
  此問題的結論只是筆者對於查詢分析的一個推論,並無相應的官方文檔可以證明,所以歡迎大家發表自己的看法



相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。