一、给结果集分页
有时候查询结果太多,会用到分页显示,如排序后每次显示5页,下面的例子是显示6到10面(第二页的数据)
SELECT rn AS 序号,ename AS 姓名,sal AS 工资 FROM
(SELECT rownum AS rn,sal,ename FROM
(SELECT sal,ename FROM emp WHERE sal IS NOT NULL ORDER BY sal)x
WHERE rownum <=10)
WHERE rn>=6;
- #最内层不直接用WHERE rownum <=10,与 sal的顺序不一样
- #WHERE rownum <=10 and WHERE rn>=6 放一起得不到数据
二、跳行取数据
前面的文章中写过随机取数据,下面求余函数MOD,来实现隔行取数据
SELECT empno, ename,sal, MOD(rn,2) FROM
(SELECT rownum AS rn, empno, ename, sal FROM
(SELECT empno,ename,sal FROM emp ORDER BY 1)x)y
WHERE MOD(rn,2)=1;
通过这个函数,想间隔几行返回都可以实现
三、简单树形查询
我人经常会用一些表来保存上下级关系,如地区表、员工表、组织机构表,为了按上下级关系递归查询这些数据,就需要用支树形查询,举个简单的例子
SELECT empno AS 员工编码,
ename AS 姓名,
mgr AS 主管编码,
(PRIOR ename) AS 主管姓名
FROM emp
START WITH empno = 7566
CONNECT BY (PRIOR empno)= mgr;