首页 > 代码库 > 分页查询
分页查询
rowmun是oracle数据库特有的内容,是一个隐含字段,为每一个查询结果集维护了一个自增的行号,行号以1开始,以1递增
不同的数据库,分特的SQL不同
mysql
limit
oracle
三层嵌套select+rownum
rownum都支持哪些操作,查询结果集必须从1开始<=, <, >0, >=1 , =1
查询前5员工
select
empno,ename,rownum
from
emp
where
rownum<=5;---
select
empno,ename,rownum
from
emp
where
rownum>0;-----所有
查询工资排名在前5名的员工
第一步:按照工资降序
select
empno,ename,sal
from
emp
order by
sal desc;
第二步:将上面的结果作为一张临时表处理
select
empno,ename,sal
from
(select
empno,ename,sal
from
emp
order by
sal desc)
where
rownum<=5;
查询员工的工资在【3-9】的员工
第一步.查询出工资排名在前9的员工:
select
empno,ename,sal
from
(select
empno,ename,sal
from
emp
order by
sal desc)
where
rownum<=9;
第二步. select
empno
from
(select
empno,ename,sal
from
emp
order by
sal desc)
where
rownum<=3;
第三部:
select
empno,ename,sal
from
(select
empno,ename,sal
from
emp
order by
sal desc)
where
rownum<=9
and empno not in(
select
empno,ename,sal
from
(select
empno,ename,sal
from
emp
order by
sal desc)
where
rownum<=5;
查询员工的工资在【3-9】的员工
第一步.查询出工资排名在前9的员工:
select
empno,ename,sal
from
(selec empno,ename,sal from emp order by sal desc)
where
rownum<=9
and
empno not in( select empno,ename,sal from (select empno,ename,sal from emp order by sal desc)where rownum<=3);
---------------------
select
empno,ename,sal
from
(selec empno,ename,sal from emp order by sal desc)
where
rownum<=9
and
empno not in( select empno,ename,sal from (select empno,ename,sal from emp order by sal desc)where linenum<=3);
通用的分页SQL
每页显示3条记录
第一页[1-3] (0-3)
第二页[4-6] (3-6)
第三页[7-9] (6-9)
select
tt.*
from
(selec t.*,rownum as linenum
from (业务SQL)t
where
rownum<=pageNo*pageSize)tt
where linenum>(pageNo-1)*pageSize;
rowmun是oracle数据库特有的内容,是一个隐含字段,为每一个查询结果集维护了一个自增的行号,行号以1开始,以1递增
不同的数据库,分特的SQL不同
mysql
limit
oracle
三层嵌套select+rownum
rownum都支持哪些操作,查询结果集必须从1开始<=, <, >0, >=1 , =1
查询前5员工
select
empno,ename,rownum
from
emp
where
rownum<=5;---
select
empno,ename,rownum
from
emp
where
rownum>0;-----所有
查询工资排名在前5名的员工
第一步:按照工资降序
select
empno,ename,sal
from
emp
order by
sal desc;
第二步:将上面的结果作为一张临时表处理
select
empno,ename,sal
from
(select
empno,ename,sal
from
emp
order by
sal desc)
where
rownum<=5;
查询员工的工资在【3-9】的员工
第一步.查询出工资排名在前9的员工:
select
empno,ename,sal
from
(select
empno,ename,sal
from
emp
order by
sal desc)
where
rownum<=9;
第二步. select
empno
from
(select
empno,ename,sal
from
emp
order by
sal desc)
where
rownum<=3;
第三部:
select
empno,ename,sal
from
(select
empno,ename,sal
from
emp
order by
sal desc)
where
rownum<=9
and empno not in(
select
empno,ename,sal
from
(select
empno,ename,sal
from
emp
order by
sal desc)
where
rownum<=5;
查询员工的工资在【3-9】的员工
第一步.查询出工资排名在前9的员工:
select
empno,ename,sal
from
(selec empno,ename,sal from emp order by sal desc)
where
rownum<=9
and
empno not in( select empno,ename,sal from (select empno,ename,sal from emp order by sal desc)where rownum<=3);
---------------------
select
empno,ename,sal
from
(selec empno,ename,sal from emp order by sal desc)
where
rownum<=9
and
empno not in( select empno,ename,sal from (select empno,ename,sal from emp order by sal desc)where linenum<=3);
通用的分页SQL
每页显示3条记录
第一页[1-3] (0-3)
第二页[4-6] (3-6)
第三页[7-9] (6-9)
select
tt.*
from
(selec t.*,rownum as linenum
from (业务SQL)t
where
rownum<=pageNo*pageSize)tt
where linenum>(pageNo-1)*pageSize;
分页查询
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。