记录： Sql 常见分页方法总结

  Sql server 2005 分页使用样例， 写下备忘。

with tblinfozero -- 随便取个表别名

as (

select artid,arttitle, author, pubtime. pubip, --筛选的字段 

row_number() over (order by artid desc) as 'rowID' 

/***** 

row_number() 函数的用法出现

注意用法： 要带上一个 over 附带上排序的字段 

*****/

from article_main

)

select * from tblinfozero 

where rowID between  20 and 30 

/***** 起止行号 *****/

    用着用着怎么都感觉到象是一个子查询？

---------------------------------------- 分割线 ----------------- 2011-1-25 ---------------------------------------

  利用 Rank 自动生成 ID 分页

select top 10 * from(
select rank() over(order by replyid desc) as rid, artid,arttitle, author, pubtime. pubip, --筛选的字段 
 from article_main 
  where replydepartmentcode = '100' 
) c where rid>10 
order by replyid desc

---------------------------------------- 分割线 ----------------- 2011-1-25 ---------------- End -----------------------


---------------------------------------- 分割线 ----------------- 2010-4-15 ---------------------------------------
昨天偶然看到 这篇文章  让我豁然开朗, 结果简单地试验,现将其他的几种分页方法记录下来:

    利用 rowcount 进行分页

/**********
利用 rowcount 进行分页 
**********/
declare @pagesize int
declare @pageindex int
set @pagesize = 20 
set @pageindex= 5

declare @startrow int 
set @startrow = (@pageindex-1) * @pagesize + 1
DECLARE @Sort int  
SET ROWCOUNT @StartRow 

SELECT @Sort = id FROM  dbo.AskOlypicChampionWord  ORDER BY  id asc
SET ROWCOUNT @pagesize
SELECT * FROM  dbo.AskOlypicChampionWord  WHERE  Id >= @Sort 
ORDER BY  id asc
SET ROWCOUNT 0 /* 取消 rowcount 设置 */

    升序-倒序 方法

/**********
升序-倒序法 
**********/
select top 20 * from ( 
    select top 40 /* 2*20  */ 
    * from article_main order by [id] asc
 dbo.) T order by [id] desc

    子查询

/**********
 子查询 分页
**********/
--第一页直接 Top
-- 第2页 及以后子查询 
select * from article_mainwhere id in(
    select top 20 id from article_main 
    where id not in(
        select top 40 id from article_main   order by id asc  
     ) order by id asc 
) order by id asc

    游标分页

/**********
游标分页
**********/

declare @tblid table(aid int) 
declare @aid int 
declare @pagesize int
declare @pageindex int  
declare @rowstart int 
set @pagesize = 20 
set @pageindex = 3
set @rowstart = (@pageindex-1) * @pagesize + 1

declare pagecursor cursor dynamic for 
    select id from article_main order by id asc
 
open pagecursor 
fetch relative @rowstart from pagecursor into @aid 
while(@@fetch_status=0 and @pagesize>0 )
begin
    insert into @tblid(aid) values(@aid)
    fetch next from pagecursor into @aid
    set @pagesize = @pagesize-1
end 
close pagecursor
deallocate pagecursor

--select * from @tblid
select * fromarticle_main  where id in(
    select aid from @tblid
) order by id asc 

另一方法是：

-- 声明变量
declare @sql nvarchar(4000) --要执行的sql语句 
declare @page int    --要显示的页码 
declare @pageSize int  --每页的大小 
declare @pageCount int --总页数 
declare @recordCount int

-- 初始化变量 
set @page = 2
set @pagesize = 20
set @pagecount = 0
set @recordCount = 0 
set @sql = 'select * from  dbo.AskOlypicChampionWord order by autoid asc'

-- 执行 Sql 语句
set nocount on 
declare @p1 int 
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output 
set @recordCount = @pageCount 
select @pagecount=ceiling(1.0*@pagecount/@pagesize) ,@page=(@page-1)*@pagesize+1 
exec sp_cursorfetch @p1,16,@page,@pagesize  
exec sp_cursorclose @p1

-- 总页数
print @recordCount


注意：此方法会产生2个表 

附录：如何算排名：

select  (select count(*)+1 as rankid from tablename b where b.autoid>a.autoid) as rankids from tablename a order by autoid desc