数据库分页查询

一、sql server分页查询

1.sql server 2012及以后

--offset/fetch next
select xxx from xxx where xxx=?
order by xxx desc
offset (@currentPage-1)*@pageSize row fetch next @pageSize rows only

2.sql server 2005及以后

--row_number()/top
select top @pageSize a.*
from (select row_number() over(order by id desc) rownumber,* from xxx) a
where a.rownumber > (@currentPage-1)*@pageSize

--row_number()
select a.*
from (select row_number() over(order by id desc) rownumber,* from xxx) a
where a.rownumber > (@currentPage-1)*@pageSize and a.rownumber <= @currentPage*@pageSize

--row_number()
select a.*
from (select row_number() over(order by id desc) rownumber,* from xxx) a
where a.rownumber between ((@currentPage-1)*@pageSize+1) and @currentPage*@pageSize

3.sql server 2005之前

--top/not in
select top @pageSize *
from xxx
where id not in (select top (@currentPage-1)*@pageSize id from xxx order by id desc)
order by id desc

--top/not exists
select top @pageSize *
from xxx a
where not exists
(select 0 from (select top (@currentPage-1)*@pageSize id from xxx order by id desc) b where b.id=a.id)
order by id desc

--top/min()
select top @pageSize *
from xxx
where id < (select min(id) from (select top (@currentPage-1)*@pageSize id from xxx order by id desc))
order by id desc

二、mysql分页查询

select * from xxx limit startIndex,pageSize

三、oracle分页查询

--ROWNUM
select *,ROWNUM
from xxx
where ROWNUM > (@currentPage-1)*@pageSize and ROWNUM <= @currentPage*@pageSize
order by id desc

--row_number()
select a.*
from (select *,row_number() over(order by id desc) rownumber from xxx) a
where a.rownumber > (@currentPage-1)*@pageSize and rownumber <= @currentPage*@pageSize