來談談SQL數據庫中"簡單的"SELECT TOP—可能有你從未注意到的細節

作者: 追索來源: 博客園發布時間: 2010-12-26 21:55 閱讀: 1900 次推薦: 1 原文鏈接 [收藏]

　　首先從博客園的Jerome Wong網友說起，，他提出了一個這樣的問題，本人寫了好幾年SQL語句了，從來沒注意到這件事情。

　　例如：

　　數據表如下：

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筆數據，測試分頁玩的。

　　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 　　Quince　　27
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這句語句是不能執行的）。

　　接下來我再舉例關于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='追索'+name
where 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%的閱讀記錄，每天只顯示符合條件正確率最高的那個：