首先從博客園的Jerome Wong網(wǎng)友說起

他提出了一個這樣的問題

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

例如：

數(shù)據(jù)表如下：

ID　　EMPNO　　NAME　　AGE　　

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

我寫了SQL語句想取得第3、4筆數(shù)據(jù)，測試分頁玩的。

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

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

取得的是：

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

但是整個SQL語句的結(jié)果卻是：

5 　　23167 　　Arishy 　　30
6 　　26371 　　Yager 　　29

真的不知道到底怎么會出現(xiàn)這種情況，請高手指教。

其實不管你是新手還是高手在寫程序當(dāng)中經(jīng)常會碰到類似這樣的細(xì)節(jié)問題

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

準(zhǔn)備工作 

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 

執(zhí)行結(jié)果大家會發(fā)現(xiàn)

平常很多人會認(rèn)為這兩條語句執(zhí)行的結(jié)果會一樣

怎么會這樣呢？

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

***種掃描完zhuisuo表后先降序(top N Sort)然后在4行范圍中取前2行

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

在這里就不得不簡單的說說SQL語句中出現(xiàn)的表子查詢了

表子查詢，而出現(xiàn)在from子句中的表我們稱為派生表

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

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

外部查詢和內(nèi)部查詢會被合并，并生成一個計劃

這時再看看上面的執(zhí)行計劃就一目了然了

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

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

派生表是個擬表要被外部引用，而order by返回的不是表而是游標(biāo).所以只用order by的話是被限制的

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

是因為top可以從order by返回的游標(biāo)里選擇指定數(shù)量生成一個表并返回

接下來我再舉例關(guān)于top需要注意的細(xì)節(jié)

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

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

經(jīng)過多次查詢后，你會失望的發(fā)現(xiàn)它沒有返回隨機行

這是因為每個查詢只調(diào)用它一次而不是每行調(diào)用它一次

這時我們可以把RAND改為Newid

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

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

2、注意insert中使用top

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

很多網(wǎng)友會解釋為把zhuisuo表中***4條插入表

但執(zhí)行完畢后又會讓你失望了，插入的是最前面的4條

正確的倒敘插入top方法應(yīng)該是

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

這兩條語句又有什么區(qū)別

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

3、有時我想刪除數(shù)據(jù)表里面時間最近的5條數(shù)據(jù)怎么辦

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

現(xiàn)在我們可以這樣來解決

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) 

這是變相實現(xiàn)Top N sort更新或刪除數(shù)據(jù) 但這不是***的方法因為這還要根具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回答過一個網(wǎng)友的問題

如何查詢某用戶近一個月內(nèi)正確率大于60%的閱讀記錄，每天只顯示符合條件正確率***的那個

在這里我只稍微提一下關(guān)于apply 也有很多有意思的細(xì)節(jié) 今后有時間我會用隨筆形式寫出來

***附上一張關(guān)于我用序號表示邏輯查詢處理的步驟 

原文鏈接：http://www.cnblogs.com/zhuisuo/archive/2010/12/23/1914790.html

【編輯推薦】

1. SQL Server 2000刪除實戰(zhàn)演習(xí)
2. SQL Server存儲過程的命名標(biāo)準(zhǔn)如何進(jìn)行？
3. 卸載SQL Server 2005組件的正確順序
4. 對SQL Server字符串?dāng)?shù)據(jù)類型的具體描述
5. SQL Server數(shù)據(jù)類型的種類與應(yīng)用