分頁時超好用的 ROW_NUMBER (Transact-SQL)

        '分頁查詢做法
        '1.先算出 condition 的欄位 rows.count 是否大於 每頁顯示筆數
        '--------------先算出總筆數--------------
        'WITH tmpTB AS (SELECT ROW_NUMBER() OVER (ORDER BY MB001) AS 筆數, MB001 AS 品號, MB002 AS 品名, MB003 AS 規格, MB080 AS 貨號
        'FROM dbo.INVMB WHERE MB001 LIKE 'BA%')
        'SELECT COUNT(*) AS 總筆數 FROM tmpTB
        '---------------------------------------
        '2.沒有，就直接列出該頁不要再跑分頁模組
        '--------------列出該頁--------------
        'WITH tmpTB AS (SELECT ROW_NUMBER() OVER (ORDER BY MB001) AS 筆數, MB001 AS 品號, MB002 AS 品名, MB003 AS 規格, MB080 AS 貨號
        'FROM dbo.INVMB WHERE MB001 LIKE 'BA%')
        'SELECT * FROM tmpTB
        'WHERE 筆數 BETWEEN ((頁數-1) * 每頁顯示筆數)) + 1 AND (頁數 * 每頁顯示筆數);
        '---------------------------------------
        '3.有，就依 ROW_NUMBER 分頁查詢列後再列出該頁
        '--------------算出總頁數--------------
        '   3.1 (總筆數 / 每頁顯示筆數) -1
        '   3.2 For Loop 將頁數顯示在物件上
        '   3.3 依照 2.列出該頁
        '---------------------------------------
        'PS. 以上做法 1. 和 2. 是 MSSQL 的語法，就套在 VB.NET 程式裡
        '    ASP.NET 就直接使用 ListView / GridView 物件功能
        '---------------------------------------
        'ROW_NUMBER 的做用，就字面上的意義，就是把每行加上序號直到最後一行
        'Refer Microsoft TechNet Library
        'http://technet.microsoft.com/zh-tw/library/ms186734.aspx