Oracle/MySql/SqlServer数据库分页

oracle | 2018-04-12 08:33:02 | 316次阅读 | 0评

Oracle

1、rownum


select * from (
    select rownum rn,* 
    from emp 
    where sj >='2018-03-01' and sj <= '2018-03-31'  and rownum<=pageNum*pageSize
) emprn 
where emprn>=(pageNum-1)*pageSize


2、row_number() over()


select * from (
    select row_number() over(order by id desc) as rn,* 
    from emp 
    where sj >='2018-03-01' and sj <= '2018-03-31'
) emprn 
where emprn.rn>=(pageNum-1)*pageSize and emprn.rn<=pageNum*pageSize


mysql

limit

select * from test 
where sj >='2018-03-01' and sj <= '2018-03-31' 
limit (pageNum-1)*pageSize,pageNum*pageSize


sqlserver

1、top 


select * 
from emp 
where  empid in (
    select top pageNum*pageSize empid 
    from emp 
    where  sj >='2018-03-01' and sj <= '2018-03-31' and empid not in (
        select top (pageNum-1)*pageSize empid 
        from emp 
        where sj >='2018-03-01' and sj <= '2018-03-31' 
    )  
order by empid) 


说明:

pageSize  :每页记录数

pageNum :页数

以上三种数据库均可通过存储过程实现分页效果

博友评论,共0条
最新评论