转:SQL Server 2005 的分页查询(ROW_NUMBER() OVER

DECLARE @rows_per_page AS INTEGER
DECLARE @current_page AS INTEGER
DECLARE @total_pages AS INTEGER
DECLARE @start_item AS INTEGER
DECLARE @items_count AS INTEGER
-- 设置每页的行数
SET @rows_per_page = 10
-- 设置要显示的页号(从1开始)
SET @current_page = 3
SELECT @total_pages = COUNT(*) / @rows_per_page + 1,
 @items_count= COUNT(*)
 FROM BarefootIndex;--表名（这是修改的地方）
--计算此页中从第几个开始显示
SET @start_item = @rows_per_page * (@current_page - 1)
select * from
(
 select ROW_NUMBER() OVER (order by ID) as item--用什么排序(返回正在显示第几条)
  ,@items_count AS items_count --一共有多少条
  ,@current_page AS current_page  --当前页
  ,@total_pages AS total_pages --一共多少页
  ,* from BarefootIndex--表名（这是修改的地方）
) as T
where T.item >= @start_item + 1
AND T.item <= @start_item + @rows_per_page

 

 

select * from
(select row_number() over (order by idsq ) as seq,pid,pmk　 from tablename ) a
where seq <= 20