分页查询SQL语句

 如果每页显示5条记录，那么第n页显示的数据记录的公式应该是：
select top 5 * from UserInfo where UserId not in
(select top (n-1)*5 UserID from UserInfo order by UserID asc)
order by UserID asc

 

select * from (select ROW_NUMBER() over(order by id) as rid,* from tab1)  as pagerTable where rid between 1000 and 1050;
        select * from (select ROW_NUMBER() over(order by id) as rid,* from tab1)  as pagerTable where rid >= 1000 and rid <= 1050;
        select * from (select top 1050 ROW_NUMBER() over(order by id) as rid,* from tab1) as pagerTable where rid >= 1000;

 

oracle的分页

select * from (select a.*,rownum rn  from tab1 a where rownum <= pagesize*page)

where rn >= pagesize*(page-1);

 

select count(1) from tab1; --总数据条数1000000
        select * from (select rownum as rid, result.* from (select * from tab1) result) where rid between 8000 and 8050;--运行时间51.657s
        select * from (select rownum as rid, result.* from (select * from tab1) result) where rid >= 8000 and rid <= 8050;--运行时间30.969s
        select * from (select rownum as rid, result.* from tab1 result where rownum <= 8050) where rid >= 8000;--运行时间0.078s