用CTE來取出指定筆數 – SQL2005

SQL2005中有個CTE (Common Table Expression)功能十分強大, 可以用來做遞迴式的參考, 藉以達成複雜的查詢邏輯並簡化查詢指令, 是 SQL2005的一個新的重要功能.

在使用 MySQL 時, 可以容易地利用 limit m, n 的語法進行指定筆數的取得, 然而在 SQL Server這裡一直沒有這種功能. 這樣的需求通常是應用在分頁上會用到. 不過SQL Server可以利用 ADO存取方式內的分頁方法來進行, 也就避開了這個問題. 但無論如何, 仍是將所有的資料帶到查詢端(如客戶端或是中間層)後, 再進行分頁, 效能仍然不佳.

以往在 SQL2000之前要達成這樣的功能, 通常是配合temp table並配合 identity 欄位達成, 但是仍需要將所有資料從第一筆到第 n 筆寫入 temp table 後, 再取 top 的方式來操作, 效能仍舊不理想.

MySQL 由於沒有查詢端的存取模型好用, 所以分頁通常都由程式自己來, 在查詢指令下 limit 後, 即可將要指定的分頁資料傳回, 不會浪費 network io 在傳遞資料上, 所以是蠻好的一個功能. 現在我們看看 CTE 要如何完成這樣的一件事.

單靠 CTE 是無法完成的, 還需要一個行號函數 ROW_NUMBER() 來協助, 以下為一個 sample:

查詢訂單資料表(orders), 依訂單金額由大而小排序, 取出第301到第400筆資料:

MySQL:

select orderid, orderdate, orderamount from orders order by orderamount desc limit 301, 400

SQL2005:

with myorder as (
select row_number() over (order by orderamount desc) as sn, orderid, orderdate, orderamount from orders )
select orderid, orderdate, orderamount from myorder where sn between 301 and 400

看起來雖然指令長了一些, 但是確實能達成查詢指定筆數的功能, 也大幅提高效能了, 如此一來, 利用 CTE 加上 row_number() 函數後, SQL2005 也能和 MySQL 一樣有指定筆數的查詢方法囉.

CTE 的功能很強大, 之後再介紹遞迴式查詢的強大功能囉!

參考資料:
http://msdn2.microsoft.com/zh-tw/library/ms175972.aspx

繼續閱讀:
http://blog.roodo.com/rocksaying/archives/2346392.html