数据库分页技术 <转>

数据库分页技术

1.mysql

--记住mysql 中limit后是从零开始的

主体格式如下：

select   *   from   tablename   limit   m,n

--比如要查第6行到第20行

select   *   from   tablename   limit   5,15

右边两种写法等价： select   *   from   table  limit  10

select   *   from   table   limit  0,  10 --都是查询前十行

limit [m,] n

m:为起始行（即从结果集的第几行开始查找），从0开始，且可以省略，即直接写 limit n这是默认从第0行开始

n:为查询条数，不能为负数

例子如下：select * from tablename limit 2,20

表示从第2行开始，取20条数据

SELECT * FROM table   LIMIT [offset,] rows | rows OFFSET offset

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定

第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法：

LIMIT # OFFSET #。

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：

mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

//如果只给定一个参数，它表示返回最大的记录行数目：

mysql> SELECT * FROM table LIMIT 5;     //检索前 5 个记录行

//换句话说，LIMIT n 等价于 LIMIT 0,n。

2.sql server

--假设id是相应表的主键字段

--排序后选出第3行到第5行

select * from ( select top 3 * from ( select  top 5 * from wen3 order by id asc ) a order by id desc ) b order by id asc ;

--一下也是对的，而且更加直观简洁，但是在sql server2000中不支持，但是在sql server2005以上版本支持，顺便说一下，oracle中已经没有except,取而代之的是minus

select top 5 * from wen3 order by id

except

select top 2 * from wen3 order by id ;

--下面我换另一种方法 因为sql server2000中没有支持except ,可以用not exists，还有not in,<> any等来替代。“except”在关系代数里面就表示“集合的差”。

 

 

 

select top 5 a.* from ( select  top 5 * from wen3 order by id asc ) a where id not in( select top 2 id  from wen3 order by id ) order by id;

3.oracle

--(方法一)这种性能是最好的

select * from (select a.*, rownum rn from (select * from table1 t order by id ) a where rownum <= 8) where rn > 4;

--或者是

select * from (select a.*, rownum rn from (select * from table1 t order by id ) a where rownum <= 8) b where b.rn > 4;

--（方法二）

select a.*,rownum from (select * from table1 t order by id ) a   --记得先排序啊

where rownum <=8

minus

select a.*,rownum from (select * from table1 t order by id ) a

where  rownum <=4 ;

--上面这种方法也很好，而且一般选手想不到哦！

--(方法三)这种性能是最差的

select * from (select a.*, rownum rn from (select * from table1 t order by id ) a )b where b.rn between 5 and 8;  --当然你这里用and可以的

--这个陷阱很多啊，小心分辨哦！

--在使用ROWNUM 时，只有当Order By 的字段是主键时，查询结果才会先

--排序再计算ROWNUM

4.DB2

--取前15条记录。

select * from 表名 order by XXX [desc] FETCH FIRST 15 ROWS ONLY

--db2中取第m---n行

select * from (select aid,aname,row_number() over(支持order by 子句) as id from 表名) as t where t.id between m and n

 

 

 

 

 

 

 

 

下面给出Oracle/Sqlserver/MySQL三种数据库先出第m至第n条记录的方法：

获取按照F1字段升序排序的，从m至n行记录

注意，F1一定要做索引或主键，最好是主键

SqlServer

select t2.* from (Select top n-m t1.* from (Select top n * from Tablename as t order by t.F1) as t1 order by t1.F1 desc) as t2 order by t2.F1

select t2.* from
(
select top 20 t1.* from
(
select top 500020 * from dbgen as t order by t.F1
)
as t1 order by t1.F1 desc
)
as t2 order by t2.F1

Oracle

select * from (select rownum r, t.* from Tablename t where r<n) t1 where t1.r > m and t1.r <= m

select * from (select rownum r, t.* from DBTEST t where rownum<300020) ss where ss.r > 300000 and ss.r <= 300020

MySQL

最简单
select * from Tablename t order by t.F1 limit n,m-n+1
select * from DBtest t order by t.F1 limit 300000,20

性能对比：
三者差不多少！
上面实例中的语句在50万条记录的表中选取第50万左右的记录20条，均不超过1秒。

 

 

 

 

 

 

Oracle、DB2、SQLSERVER、Mysql、Access分页SQL语句梳理

Oracle、DB2、SQLSERVER、Mysql、Access分页SQL语句梳理
最*把*时在项目中常用到的数据库分页sql总结了下。大家可以贴出分页更高效的sql语句。
sqlserver分页
第一种分页方法
需用到的参数:
pageSize 每页显示多少条数据
pageNumber 页数 从客户端传来
totalRecouds 表中的总记录数 select count (*) from 表名
totalPages 总页数
totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1
pages 计算前pages 条数据
pages= pageSize*(pageNumber-1)
SQL语句:
select top pageSize * from 表名 where id not in (select top pages id from 表名 order by id) order by id
第二种分页方法
pageSize 每页显示多少条数据
pageNumber 页数 从客户端传来
pages=pageSize*(pageNumber-1)+1
select top pageSize * from 表名 where id>=(select max(id) from (select top pages id from 表名 order by id asc ) t )

mysql分页
需用到的参数:
pageSize 每页显示多少条数据
pageNumber 页数 从客户端传来
totalRecouds 表中的总记录数 select count (*) from 表名
totalPages 总页数
totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1
pages 起始位置
pages= pageSize*(pageNumber-1)
SQL语句:
select * from 表名 limit pages, pageSize;
mysql 分页依赖于关键字 limit 它需两个参数:起始位置和pageSize
起始位置=页大小*(页数-1)
起始位置=pageSize*(pageNumber -1)

oracle分页
pageSize 每页显示多少条数据
pageNumber 页数 从客户端传来
totalRecouds 表中的总记录数 select count (*) from 表名
totalPages 总页数
totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1
startPage 起始位置
startPage= pageSize*(pageNumber-1)+1
endPage=startPage+pageSize
SQL语句
select a.* from
(
   select rownum num ,t.* from 表名 t where 某列=某值 order by id asc
)a
where a.num>=startPage and a.num<endPage

db2分页
int startPage=1 //起始页
int endPage;     //终止页
int pageSize=5; //页大小
int pageNumber=1 //请求页

startPage=(pageNumber-1)*pageSize+1
endPage=(startPage+pageSize);

SQL语句
select * from (select 字段1,字段2,字段3,字段4,字段5,rownumber() over(order by 排序字段 asc ) as rowid from 表名 )as a where a.rowid >= startPage AND a.rowid <endPage

access分页
pageSize 每页显示多少条数据
pageNumber 页数 从客户端传来
pages=pageSize*(pageNumber-1)+1
SQL语句
select top pageSize * from 表名 where id>=(select max(id) from (select top pages id from 表名 order by id asc ) t )