Oracle、DB2、SQLSERVER、Mysql、Access分页SQL语句梳理
最近把平时在项目中常用到的数据库分页sql总结了下。大家可以贴出分页更高效的sql语句。
sql server分页
- 第一种分页方法
需用到的参数:
pageSize | 每页显示多少条数据 |
pageNumber | 页数(从客户端传来) |
totalRecouds | 表中的总记录数 select count (*) from tableName |
totalPages | 总页数(totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1 ) |
pages | 计算前pages条数据(pages= pageSize*(pageNumber-1)) |
SQL语句:
select top pageSize * from tableName where id not in (select top pages id from tableName order by id) order by id
- 第二种分页方法
需用到的参数:
pageSize | 每页显示多少条数据 |
pageNumber | 页数(从客户端传来) |
pages | pageSize*(pageNumber-1)+1 |
SQL语句:
select top pageSize * from tableName where id>=(select max(id) from (select top pages id from tableName order by id asc ) t )
mysql分页
SQL语句:
select * from tableName limit startOffset, pageSize;
mysql 分页依赖于关键字 limit 它需两个参数:起始位置和每页显示的记录条数
oracle分页
SQL语句:
select a.* from
(
select rownum num ,t.* from tableName t where columnName=value order by id asc
) a
where a.num>=startOffset and a.num<endOffset
db2分页
SQL语句
select * from
(select column1,column2,column3,column4,column5,rownumber() over(order by sortColumn asc ) as rowid from tableName )as a
where a.rowid >= startOffset AND a.rowid <endOffset
access分页
rows | 每页显示多少条数据 |
pages | 当前显示的页码 |
SQL语句
select top rows * from tableName where id>=(select max(id) from (select top pages id from tableName order by id asc ) t )
博文来源: http://www.blogjava.net/sxyx2008/archive/2010/09/16/332193.html