mysql、SQLServer、Oracle分页总结

分页查询

MySQL分页查询

select * 　from tableName where 条件 limit (从第几条取，一共取几条);

SQLServer 分页查询

select top 4 * from tableName where id not in (select top 4 id from tableName where 条件);

意思：排除前4条，再取4条记录。实际从第5条取到第8条。

详细说明：

select top 4 取前四条记录

not in (select top 4 列名 from tableName where 条件 ) ： 这里 top 4 说明排除掉前 4条数据，从第五条开始取数据

select top 4 * from 表名 where 列名 not in (select top 4 列名 from 表名 where 条件);

红色的标注为关键字，必需有。

Oracle分页查询

特别注意： oracle 每张表 都有 rownum 默认字段 , 默认情况下不显示 , 但是是一直存在的。

案列： select t2.* from ( select t1.* , rownum rn from ( select * from emp ) t1 where rownum <= 6) t2 where rn >= 4;

① select * from emp ； // 第一张表，查询到表中所有数据

② select t1.* , rownum rn from

( select * from emp ) t1 where rownum <= 6； // 第二张表 ， 把表一查到的数据看成是一张临时表从中取出 6 条数据 ， 并形成第二张临时表。

③ select t2.* from ( select t1.* , rownum rn from ( select * from emp ) t1 where rownum <= 6) t2 where rn >= 4; // 第三张表 , 把表二中 看到的 6条数据看成是临时表，并从这6条数据中 从第4条取到第6条数据 ， 再形成第三张临时表 ， 也就是我们最终要的结果。

分页查询语法：

select t2.*

from (select t1.*, rownum rn

from (select * from 表名) t1

where rownum <= 大范围 取到多少条数据) t2

where rn >= 小范围 从第几条数据开始取;

特别说明： oracle分页是通过三层 筛选法进行查询的。每一次都可以带where条件来对要查询的信息进行刷选。

建议： 针对不同情况的分页查询,尽可能的在最内层(第一层)设置条件，包括多表分页；

第二层设置最大取值范围；第三层从第几条数据开始取值。

上面的这个sql分页查询模版是oracle数据库效率比较高的查询方法，在百万数据级别对数据进行查询都可以及时响应。

思考：按照入职时间顺序 ， 查询从第 7 到 第10 个人 是谁？

select t2.* from

(select t1.*, rownum rn from

(select * from emp order by hiredate ASC ) t1 where rownum <=10 ) t2 where rn >=7;

看看分页查询的效率

模拟 10 万 条数据的一个表：

// 以存在的 表为模板 ， 创建表

create table mytest as select empno ， ename ， sal ， comm ，deptno from emp ;

// 自我复制 (蠕虫复制)

insert into mytest (empno ， ename ， sal ， comm ，deptno ) se lect empno ， ename ， sal ， comm ，deptno from mytest ;

oracle的分页查询共三种方法

1、根据ROWID来分页(速率一般)

select * from emp where rowid in (select rid from (select rownum rn,rid from (select rowid rid,cid from emp order by cid desc) where rownum<10000) where rn>9980) order by cid desc;

看得更清楚点

select * from emp where rowid in

    (select rid from

        (select rownum rn,rid from

            (select rowid rid,cid from emp order by cid desc)

        where rownum<10000)

    where rn>9980)

order by cid desc;

2、按分析函数来分页(速率慢)

select * from (select t.*,row_number() over(order by cid desc) rk from emp t) where rk<10000 and rk>9980;

 

3、按rownum来分页(推荐使用速率稳定且速率快)

select t2.* from (select t1.*,rownum rn from (select * from emp) t1 where rn<=10000) t2 where rn>=9980;

看得更清楚一点

select t2.* from

    (select t1.*,rownum rn from

        (select * from emp)

    t1 where rn<=10000)

t2 where rn>=9980;