SQL2005增加了不少新特性，其中NTILE和ROW_NUMER使得我們不再為SQL如何靈活方便的分頁(yè)傷腦筋了（不必再羨慕Oracle等數(shù)據(jù)庫(kù)了）。下面就是一個(gè)很簡(jiǎn)單的分頁(yè)查詢語(yǔ)句： DECLARE @rows_per_page AS INTEGERDECLARE @current_page AS INTEGERDECLARE @total_pages AS INTEGER

-- 設(shè)置每頁(yè)的行數(shù)SET @rows_per_page = 20-- 設(shè)置要顯示的頁(yè)號(hào)(從1開(kāi)始)SET @current_page = 2-- 計(jì)算總頁(yè)數(shù)SELECT @total_pages = COUNT(*) / @rows_per_pageFROM testtable;

-- 列出指定頁(yè)的內(nèi)容WITH t AS( SELECT NTILE(@total_pages) OVER(ORDER BY id) AS page_number, * FROM testtable)SELECT * from t WHERE page_number = @current_page 程序簡(jiǎn)單到可以不用說(shuō)明的程度。我們可以利用上述簡(jiǎn)單的語(yǔ)句，變化排序條件和查詢表，就可以做出一個(gè)很通用的分頁(yè)查詢的存儲(chǔ)過(guò)程或查詢語(yǔ)句了。

同樣的，使用ROW_NUMBER也可以做到分頁(yè)查詢： DECLARE @rows_per_page AS INTEGERDECLARE @current_page AS INTEGERDECLARE @start_row_num AS INTEGER

-- 設(shè)置每頁(yè)的行數(shù)SET @rows_per_page = 20-- 設(shè)置要顯示的頁(yè)號(hào)(從1開(kāi)始)SET @current_page = 2-- 設(shè)置開(kāi)始行號(hào)SET @start_row_num = (@current_page - 1) * @rows_per_page

WITH t AS( SELECT ROW_NUMBER() OVER(ORDER BY id) AS row_number, * FROM testtable)SELECT * from t WHERE row_number BETWEEN @start_row_num AND @start_row_num + @rows_per_page似乎更簡(jiǎn)單的樣子。

至于哪種效率更高，這需要大家實(shí)際測(cè)試了。

http://ly4cn.cnblogs.com/archive/2006/07/11/447905.html