Sql 分页语句

  with temptb  as
  (select ROW_NUMBER() over (order by ID) as RowID,* from PageCut)
  select * from temptb where RowID between 2*50 and 2*50 +50

 

这是一个数据分页方法，从SQL2005起就支持了

 

这里要记住一点的是with temptb  as 这个方法可以对查询出来的结果作为一张新表再次查询

 

这个在某些时候很有用

 

Edit:2010.03.18

 

ALTER procedure [dbo].[spCheckMainSubject]
@PageSize int,
@PageIndex int
as
begin
 with #temp  as
 (
  select top(@PageIndex*@PageSize) ROW_NUMBER() over (order by ID Desc) as RowID,* from Article
 )
 select * from #temp where RowID between @PageSize*(@PageIndex-1) and @PageSize*@PageIndex
end

 

加上一个top的话，查询更加的快，一次性十万条都很快，比起单独用top来控制更有效