MS SQL - 排序函數：ROW_NUMBER()、RANK() 與 DENSE_RANK()

這三個排序函數是MS SQL 2005之後的版本才有的，雖然公司寫的是PHP，但還是會遇到客戶使用MS SQL
然而最近遇到了排序會亂掉的問題，所以就上網搜尋了一下資料，於是在【旗標知識網】查到了一些很有用的資料，特別在此紀錄一下，希望【旗標】的大大不要生氣^^"

針對資料表的欄位進行排序
ROW_NUMBER() 可以依照指定的欄位將所有記錄進行排序, 然後再依照順利為每一筆記錄給 定一個序號，但如果指定的欄位資料一樣時，會依照其他依據來決定排名，因此名次不會相同。 以下是借用原文章的SQL語法 SELECT 書籍編號, 書籍名稱, 價格, 出版公司, ROW_NUMBER() OVER(ORDER BY 價格) AS 價格排名 FROM 書籍 而 RANK()函數遇到相同的數值會給相同的排名, 其後的排名則會跳過。例如有三個第 2 名 時, 就不會有第 3 及第 4 名, 它會從第 5 名開始。RANK() 的用法與 ROW_UNMBER() 是類似的。 SELECT 書籍編號, 書籍名稱, 價格, 出版公司, RANK() OVER(ORDER BY 價格) AS 價格排名 FROM 書籍 如果排名不想被自動跳過的話可以使用DENSE_RANK()函數。 SELECT 書籍編號, 書籍名稱, 價格, 出版公司, DENSE_RANK() OVER(ORDER BY 價格) AS 價格排名 FROM 書籍 指定查詢範圍
排序函數除了可以將所有記錄進行排序, 還能夠指定查詢第 M 筆到第 N 筆的記錄。我之前曾經寫過SQL 2000版本的語法，這裡我RANK()來撰寫，由於原文的例子似乎不是程式的語法，所以在這改用自己公司寫的例子來給大家看看 SELECT t2.*, b.title as sfm_title FROM ( SELECT rank() OVER ( ORDER BY t1.id DESC ) AS RankNumber, * FROM ( select * from focus_detail where delete='F' ) AS t1 )AS t2 inner join focus_master b on b.id=t2.sfm_id WHERE RankNumber between 11 and 20 上面這個例子各位要注意一下inner join的位置，如果你是擺在最內層join的話，是沒問題的，但如果你像我一樣是擺在最外層才join，最後排出來的順序會亂跳，所以必須在最後加上order by 的語法將取得的資料排程你所需要的順序 SELECT t2.*, b.title as sfm_title FROM ( SELECT rank() OVER ( ORDER BY t1.id DESC ) AS RankNumber, * FROM ( select * from focus_detail where delete='F' ) AS t1 )AS t2 inner join focus_master b on b.id=t2.sfm_id WHERE RankNumber between 11 and 20 ORDER BY t1.id DESC

MS SQL - IN and EXISTS

通常我們要取得某幾筆資料的時候都會使用IN(NOT IN)來當作WHERE條件
但是隨著資料庫愈來愈大時，使用IN(NOT IN)效率就會相對的降低
這時候我們可以使用另一個語法...EXISTS(NOT EXISTS)
我們直接用範例來說明，大家應該會比較清楚
以下為範例『 總共300筆資料,1頁20筆資料,第3頁 』的狀況表示SQL的主體原型 select * from (select top 60 pro_id,pro_name from product order by pro_id asc) aa where not exists(select 1 from (select top 40 pro_id from product order by pro_id asc) bb where aa.pro_id=bb.pro_id) 如果上面還看不是很懂，請看下面分解說明：
〝select top 60 pro_id,pro_name from product order by pro_id asc〞--假設為A。
這一段是表示一頁20筆,第三頁的狀況,所以總共要找出1~60筆的資料出來(20*3=60)。
〝select top 40 pro_id from product order by pro_id asc〞--假設為B。
這一段是表示一頁20筆,第三頁的狀況總共要找出1~40筆的資料出來(20*2=60)。
組合上列兩項結果套用在下列公式就非常簡易了。
【公式】總共資料範圍( A ) - 不要出現的範圍( B ) = 出現的範圍( 第3頁的20筆資料 )
【實作】select * from (A) aa where not exists(select 1 from (B) bb where aa.id=bb.id)

以上是借用Dr.陳與徐助教的範例
如有問題，請找他們...^+++++++++++++^

不過MS SQL 2005之後有出現新的語法 RANK() OVER
等我研究之後才放上來跟大家討論一下