--测试分页
declare @I int,@S varchar(50),@pageNumber int ,@pageSize int,@SQL varchar(4000),@ADate datetime
if 1=1 --设为1=0 可不用初始化临时数据。
begin
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.TT') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table TT

create table TT(_id int primary key,_Name varchar(50),_Date datetime)

select @i=1,@ADate=convert(datetime,convert(int,GetDate()))
while @i<10000
begin

insert TT values(@i,convert(varchar,@i)+'.这是一个测试数据。索引为：'+convert(varchar,@i), @ADate )

select @i=@i+1,@ADate=DateAdd(day,1,@ADate)

end

end

select @PageNumber = 3,@PageSize= 20

Select @SQL = '
Select TT_Final._id,TT_Final._Name,TT_Final._Date
from
--这是一个将正序结果再反序的表
(select top '+Convert(Varchar,@PageSize)+' _id,_name ,_Date
from
--这是一个正序的表
(select top '+Convert(varchar,@PageNumber*@PageSize)+' _id ,_Name,_Date from TT order by _Date ASC ) As
TT_DESC Order By TT_DESC._Date DESC ) AS
TT_Final
ORDER BY TT_Final._id ASC
'
Exec(@SQL)