SQL Server數(shù)據(jù)庫(kù)中簡(jiǎn)單的SELECT TOP
首先從博客園的Jerome Wong網(wǎng)友說起
他提出了一個(gè)這樣的問題
本人寫了好幾年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ù)，測(cè)試分頁玩的。
- 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語句的結(jié)果卻是：
5 23167 Arishy 30
6 26371 Yager 29
真的不知道到底怎么會(huì)出現(xiàn)這種情況，請(qǐng)高手指教。
其實(shí)不管你是新手還是高手在寫程序當(dāng)中經(jīng)常會(huì)碰到類似這樣的細(xì)節(jié)問題
下面我就對(duì)Jerome Wong網(wǎng)友所提出的問題針對(duì)select top做出一系列的分析(在這里要感謝Jerome Wong網(wǎng)友提出的這個(gè)問題)
準(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
下面我們來簡(jiǎn)單寫兩句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é)果大家會(huì)發(fā)現(xiàn)
平常很多人會(huì)認(rèn)為這兩條語句執(zhí)行的結(jié)果會(huì)一樣
怎么會(huì)這樣呢？
從這個(gè)查詢計(jì)劃中大家可以清楚的看到
***種掃描完zhuisuo表后先降序(top N Sort)然后在4行范圍中取前2行
第二種掃描完zhuisuo表后先升序取4行(top N Sort)然后再把這4行降序取2行(top N Sort)
在這里就不得不簡(jiǎn)單的說說SQL語句中出現(xiàn)的表子查詢了
表子查詢，而出現(xiàn)在from子句中的表我們稱為派生表
派生表是虛擬的，未被物理具體化，也就是說當(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
也就是說select top 2 * from (select * from zhuisuo order by id asc) m order by m.id desc這句語句是不能執(zhí)行的）
派生表是個(gè)擬表要被外部引用，而order by返回的不是表而是游標(biāo).所以只用order by的話是被限制的
然而為什么使用top加order by又可以了
是因?yàn)閠op可以從order by返回的游標(biāo)里選擇指定數(shù)量生成一個(gè)表并返回
接下來我再舉例關(guān)于top需要注意的細(xì)節(jié)
1、使用top返回隨機(jī)行，很多人會(huì)想到用RAND函數(shù)從而得到這樣一個(gè)語句
- select top 4 id,name from zhuisuo order by rand();
經(jīng)過多次查詢后，你會(huì)失望的發(fā)現(xiàn)它沒有返回隨機(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
這兩條語句又有什么區(qū)別
其實(shí)第上面那條語句更本就沒有排序(Top N Sort)
3、有時(shí)我想刪除數(shù)據(jù)表里面時(shí)間最近的5條數(shù)據(jù)怎么辦
delete 和update使用top的時(shí)候不能使用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)
這是變相實(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回答過一個(gè)網(wǎng)友的問題
如何查詢某用戶近一個(gè)月內(nèi)正確率大于60%的閱讀記錄，每天只顯示符合條件正確率***的那個(gè)
在這里我只稍微提一下關(guān)于apply 也有很多有意思的細(xì)節(jié) 今后有時(shí)間我會(huì)用隨筆形式寫出來
***附上一張關(guān)于我用序號(hào)表示邏輯查詢處理的步驟
原文鏈接：http://www.cnblogs.com/zhuisuo/archive/2010/12/23/1914790.html
【編輯推薦】
