SQL之层次查询

用途--查询上下级

查询职位为总裁的雇员及其下属的相关信息

select empno, ename, job, mgr, deptno, level
  from emp
 start with job = 'PRESIDENT'
connect by prior empno = mgr;

 

 

 

格式化

select empno,
       cast(lpad(' ', level * 2 - 1, ' ') || ename as varchar2(20)) ename,
       job,
       mgr,
       deptno,
       level
  from emp
 start with job = 'PRESIDENT'
connect by prior empno = mgr;

 

 

 

select empno,
       cast(lpad(' ', level * 2 - 1, ' ') || ename as varchar2(20)) ename,
       job,
       mgr,
       deptno,
       level
  from emp
 start with empno = 7788
connect by empno = prior mgr;

 

 以雇员编号为7788的员工为根查出所有上下级信息

select empno,
       cast(lpad(' ', level * 2 - 1, ' ') || ename as varchar2(20)) ename,
       job,
       mgr,
       deptno,
       level,
       case
         when prior empno = mgr then
          0
         else
          1
       end flag
  from emp
 start with empno = 7788
connect by nocycle prior empno = mgr
        or empno = prior mgr;

 

 

用途--构造序列

等差数列和等比数列

select rownum rn from dual connect by rownum<5;
select rownum+15 rn from dual connect by rownum<5;
select 3*rownum rn from dual connect by rownum<5;

select power(2,rownum) rn from dual connect by rownum<5;

有11种不同的方法可以将20表示成8个正奇数之和。列出所有的可能性

with t as
 (select rownum * 2 - 1 r from dual connect by rownum <= 7)
select *
  from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8
 where t1.r + t2.r + t3.r + t4.r + t5.r + t6.r + t7.r + t8.r = 20
   and t1.r <= t2.r
   and t3.r <= t4.r
   and t5.r <= t6.r
   and t7.r <= t8.r
   and t3.r >= t2.r
   and t5.r >= t4.r
   and t7.r >= t6.r;

 

 用途--连接字符串

从名为KING的雇员开始，查出所有下级雇员相关信息以及雇员名路径（用'/'连接）

select empno,
       cast(lpad(' ', level * 2 - 1, ' ') || ename as varchar2(20)) ename,
       job,
       mgr,
       deptno,
       level,
       cast(sys_connect_by_path(ename,'/') as varchar2(40)) namepath
  from emp
 start with ename = 'KING'
connect by prior empno = mgr;

 

 26个大写英文字母连接

with t as
(select ascii('A')+rownum-1 aa from dual connect by rownum<=26),
u as (select aa,chr(aa) bb from t)
select replace(max(sys_connect_by_path(bb,',')),',') from u start with aa=ascii('A') connect by prior aa=aa-1;

 伪列

level

rownum

connect_by_isleaf 如果是叶子时返回1

connect_by_iscycle

操作符

prior 得到之前的数据

connect_by_root 得到根节点

条件

where 事后过滤，下面2个完成后过滤

start with 决定从哪些节点开始查询，开始构造树

connect by 决定节点的连接条件

这三个地方都可以写上条件

排序

order by

order siblings by 排序同一节点下的数据