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

他提出了一個(gè)這樣的問(wèn)題

本人寫了好幾年SQL語(yǔ)句了，從來(lái)沒(méi)注意到這件事情。

例如：

數(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語(yǔ)句想取得第3、4筆數(shù)據(jù)，測(cè)試分頁(yè)玩的。

 
 
 
 

  
  
  
  
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

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

5 　　23167 　　Arishy 　　30
6 　　26371 　　Yager 　　29

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

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

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

準(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 
 
 
 
 

下面我們來(lái)簡(jiǎn)單寫兩句Select語(yǔ)句

 
 
 
 

  
  
  
  
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é)果大家會(huì)發(fā)現(xiàn)

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

怎么會(huì)這樣呢？

從這個(gè)查詢計(jì)劃中大家可以清楚的看到

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

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

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

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

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

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

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

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

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

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

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

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

是因?yàn)閠op可以從order by返回的游標(biāo)里選擇指定數(shù)量生成一個(gè)表并返回

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

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

 
 
 
 

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

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

這是因?yàn)槊總€(gè)查詢只調(diào)用它一次而不是每行調(diào)用它一次

這時(shí)我們可以把RAND改為Newid

 
 
 
 

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

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

2、注意insert中使用top

 
 
 
 

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

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

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

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

 
 
 
 

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

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

其實(shí)第上面那條語(yǔ)句更本就沒(méi)有排序(Top N Sort)

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

delete 和update使用top的時(shí)候不能使用order by

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

 
 
 
 

  
  
  
  
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) 
 
 
 
 

這是變相實(shí)現(xiàn)Top N sort更新或刪除數(shù)據(jù) 但這不是***的方法因?yàn)檫@還要根具id去匹配

這時(shí)我們可以使用這種方法

 
 
 
 

  
  
  
  
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回答過(guò)一個(gè)網(wǎng)友的問(wèn)題

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

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

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

網(wǎng)站名稱：SQLServer數(shù)據(jù)庫(kù)中簡(jiǎn)單的SELECTTOP
文章URL：http://uogjgqi.cn/article/djoijgs.html