记录: Sql 常见分页方法总结
Sql server 2005 分页使用样例, 写下备忘。
sql 分页语句
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
/***** 起止行号 *****/
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,
where replydepartmentcode = '100'
) c where rid>10
order by replyid desc
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 进行分页
/**********
利用 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 设置 */
利用 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
升序-倒序法
**********/
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
子查询 分页
**********/
--第一页直接 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 @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
另一方法是:
使用 sp_cursor* 分页
-- 声明变量
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个表
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