SQL Server資料庫中簡單的SELECT TOP

首先從部落格園的Jerome Wong網友說起

他提出了一個這樣的問題

本人寫了好幾年SQL語句了，從來沒注意到這件事情。

例如：

資料表如下：

IDEMPNONAMEAGE

1 26929 Jerome 　 28
2 28394 Quince27
3 20983 Green 30
4 27189 Mike   30
5 23167 Arishy 30
6 26371 Yager 29

我寫了SQL語句想取得第3、4筆資料，測試分頁玩的。

 

  
select  top 2 * from (select top 4 * from Member ) m    
  
order by m.RowID desc 
 

我執行中間那一段子查詢：select top 4 * from Member

取得的是：

1 26929 Jerome 　 28
2 28394 Quince27
3 20983 Green 30
4 27189 Mike   30

但是整個SQL語句的結果卻是：

5 23167 Arishy 30
6 26371 Yager 29

真的不知道到底怎麼會出現這種情況，請高手指教。

其實不管你是新手還是高手在寫程式當中經常會碰到類似這樣的細節問題

下面我就對Jerome Wong網友所提出的問題針對select top做出一系列的分析(在這裡要感謝Jerome Wong網友提出的這個問題)

準備工作 

 

  
if object_id('zhuisuo')is not null 
  
drop table zhuisuo  
  
go  
  
create table zhuisuo  
  
(  
  
id int null,name varchar(20) null 
  
)  
  
insert into zhuisuo values(1,'追索1')  
  
insert into zhuisuo values(2,'追索2')  
  
insert into zhuisuo values(3,'追索3')  
  
insert into zhuisuo values(4,'追索4')  
  
insert into zhuisuo values(5,'追索5')  
  
insert into zhuisuo values(6,'追索6')  
  
insert into zhuisuo values(7,'追索7')  
  
insert into zhuisuo values(8,'追索8')  
  
insert into zhuisuo values(9,'追索9')  
  
insert into zhuisuo values(10,'追索10')  
  
go 
 

下面我們來簡單寫兩句Select語句

 

  
select top 2 * from (select top 4 * from zhuisuo) m order by m.id desc 
  
select top 2 * from (select top 4 * from zhuisuo order by id asc) m order by m.id desc 
 

執行結果大家會發現

平常很多人會認為這兩條語句執行的結果會一樣

怎麼會這樣呢？

從這個查詢計劃中大家可以清楚的看到

第一種掃描完zhuisuo表後先降序(top N Sort)然後在4行範圍中取前2行

第二種掃描完zhuisuo表後先升序取4行(top N Sort)然後再把這4行降序取2行(top N Sort)

在這裡就不得不簡單的說說SQL語句中出現的表子查詢了

表子查詢，而出現在from子句中的表我們稱為派生表

派生表是虛擬，未被物理具體化，也就是說當編譯的時候

如(select top 2 * from (select top 4 * from zhuisuo) m order by m.id desc )

外部查詢和內部查詢會被合并，並產生一個計劃

這時再看看上面的執行計畫就一目瞭然了

注意事項：在派生表裡面一般不允許使用order by除非指定了top

也就是說select top 2 * from (select * from zhuisuo order by id asc) m order by m.id desc這句語句是不能執行的）

派生表是個擬表要被外部參考，而order by返回的不是表而是遊標.所以只用order by的話是被限制的

然而為什麼使用top加order by又可以了

是因為top可以從order by返回的遊標裡選擇指定數量產生一個表並返回

接下來我再舉例關於top需要注意的細節

1、使用top返回隨機行，很多人會想到用RAND函數從而得到這樣一個語句

 

  
select top 4 id,name from zhuisuo order by rand(); 
 

經過多次查詢後，你會失望的發現它沒有返回隨機行

這是因為每個查詢只調用它一次而不是每行調用它一次

這時我們可以把RAND改為Newid

 

  
select top 4 id,name from zhuisuo order by newid(); 
 

這時就會得到你想要的結果了，在這裡我們可以意識到NEWID具有更好的分布特性

2、注意insert中使用top

 

  
insert top (4) into zhuisuo  
  
select * from zhuisuo order by id desc 
 

很多網友會解釋為把zhuisuo表中最後4條插入表

但執行完畢後又會讓你失望了，插入的是最前面的4條

正確的倒敘插入top方法應該是

 

  
insert into zhuisuo  
  
select  top (4) * from zhuisuo order by id desc 
 

這兩條語句又有什麼區別

其實第上面那條語句更本就沒有排序(Top N Sort)

3、有時我想刪除資料表裡面時間最近的5條資料怎麼辦

delete 和update使用top的時候不能使用order by

現在我們可以這樣來解決

 

  
delete zhuisuo  
  
where id in (select top(5) id from zhuisuo order by id desc)   
  
update zhuisuo  
  
set name='追索'+namewhere id in (select top(5) id from zhuisuo order by id desc) 
 

這是變相實現Top N sort更新或刪除資料 但這不是最優的方法因為這還要根具id去匹配

這時我們可以使用這種方法

 

  
with cte_del as(select top(5) *   
  
from zhuisuo order by id desc)  
  
delete from cte_del  
  
with cte_del as 
  
(select top(5) * from zhuisuo order by id desc)  
  
update  cte_del set name='追索'+name 
 

4、top除了這些還有更多的用處，就比如之前我使用Top N sort 加 apply回答過一個網友的問題

如何查詢某使用者近一個月內正確率大於60%的閱讀記錄，每天只顯示符合條件正確率最高的那個

在這裡我只稍微提一下關於apply 也有很多有意思的細節 今後有時間我會用隨筆形式寫出來

最後附上一張關於我用序號表示邏輯查詢處理的步驟 

編輯精選】