SQL Server分页

2000及2005通用分页。

1:3次排序分页。
select * from (
select top 10 * from
(
select top 20 * from  
SourceTable order by [id] asc
) as TempTable order by [id] desc
) as RetTable order by [id] asc

2：双TOP分页。

select top 10 * from SourceTable
where [Id] not in
(
   select top 10 [Id] from SourceTable
)

3：MAX分页。

select top 10 * from SourctTable
where [Id] > (
select max([Id])from (select top 20 [Id] from SourctTable order by [Id]) as TempTable
)

 

2005新函数分页：

 

1：row_number分页。

select * from (
select row_number()over(order by [Id]) as RowIndex , * from SourceTable
) as TempTable where RowIndex between 10 and 100

2：