[SQL] sql分頁

分頁sql參考

1.sqlserver 2012 以上－使用offset fetch next  (只是之前在百萬筆資料時，讀取最後頁面此法慢慢的QQ)

                        WITH K AS                           

                       (
                            SELECT {0}
                         FROM Data t
                            where t.IsPublish = 1 
                         {4}
                         ORDER BY {0} {1}
                         OFFSET ({2} - 1) * {3} ROWS FETCH NEXT {3} ROWS ONLY 
                        )
                        SELECT c.[ID]
                        FROM dbo.Data AS c
                        INNER JOIN K ON c.{0} = K.{0}
                        ORDER BY c.{0} {1};

2. 之前使用的 ROW_NUMBER()

                       select *
                        from (
                         SELECT
                          t.ID,
                          ROW_NUMBER() OVER ( ORDER BY  t.{0} {1} ) RN
                         FROM Data t
                         where t.IsPublish = 1 
                         {4} 
                        ) data
                        WHERE RN BETWEEN {2} AND {3} ORDER BY RN";