select threadid from (select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads) as T where T.Pos > 100000 and T.Pos < 100030

===========================================

如果里面的這個表cs_threads數(shù)據(jù)量超大，比如，幾億條記錄，那這個方法應(yīng)該是有問題的

因?yàn)椋瑂elect threadid from ( select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads ) as T where T.Pos > 100000 and T.Pos < 100030 這個語句是把select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads它全部取出來，然后在SQL的外面進(jìn)行分頁的，沒在SQL2005上測試過，因?yàn)樵仍贠RACLE上這樣的寫法是不好的，ORACLE中這樣寫比較好：select threadid from ( select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads a where a.pos<100030 ) as T where T.Pos > 100000

出處：blog.joycode.com/dotey/archive/2006/01/16/70493.aspx