澳门新葡亰,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 :页数

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

网站地图xml地图