一、分页公式:
[b]所需总页数=(总记录数+每页显示记录数-1)/每页显示记录数
游标定位于结果集中的行号=(当前页号-1)*每页显示记录数[/b]
二、SQL Server:
[img]/upload/attachment/21163/67a5b00c-f888-382a-93c4-31290aab467c.gif[/img]
[img]/upload/attachment/21161/7aa636d4-2279-384d-a1c6-7497b3066dd2.gif[/img]
三、Oracle:
[img]/upload/attachment/21159/f03fa393-1d05-3064-a89e-ede24875c973.gif[/img]
返回有条件且经过排序的某段记录(三层结构)
返回最后1行记录
返回倒数第10行记录
[b]所需总页数=(总记录数+每页显示记录数-1)/每页显示记录数
游标定位于结果集中的行号=(当前页号-1)*每页显示记录数[/b]
int beginRowNum = (pageNo * pageSize) - pageSize + 1;//开始行号=(页号*每页记录数)-每页记录数+1
int endRowNum = pageNo * pageSize;//结束行号=页号*每页记录数
二、SQL Server:
create procedure proc_page
@curpage int, --当前页号
@pagesize int, --每页显示记录数
@count int output --总记录数(输出参数)
as
declare @temp int
set @temp=(@curpage-1)*@pagesize --游标定位于结果集中的行号
select @count=count(*) from categories
declare @sql varchar(200)
set @sql='select top '+convert(varchar(30),@pagesize)+' * from categories where categoryid not in (select top '+convert(varchar(30),@temp)+' categoryid from categories)'
exec(@sql)
print @sql
go
select * from categories
exec proc_page 1,5,2
exec proc_page 2,5,2
[img]/upload/attachment/21163/67a5b00c-f888-382a-93c4-31290aab467c.gif[/img]
[img]/upload/attachment/21161/7aa636d4-2279-384d-a1c6-7497b3066dd2.gif[/img]
三、Oracle:
select * from (select rownum r,col1,col2 from tab) where r>=2 and r<=9;
[img]/upload/attachment/21159/f03fa393-1d05-3064-a89e-ede24875c973.gif[/img]
返回有条件且经过排序的某段记录(三层结构)
select *
from (select u.*, rownum num
from (select t.*
from userinfo t
where user_id = 73
order by reg_date desc) u
where rownum <= 6)
where num >= 4;
返回最后1行记录
select *
from (select t.*, rownum num from userinfo t)
where num = (select count(*) from userinfo);
返回倒数第10行记录
select *
from (select t.*, rownum num from userinfo t)
where num = (select count(*) - 10 from userinfo);