树结构的查询start with子句

1使用start with语法来处理层次结构的数据

层次结构的数据查询

select empno,ename,mgr from emp

start with (ename='SMITH')

connect by prior mgr=empno;

EMPNO ENAME MGR

----- ---------- ----------

7369 SMITH 7902

7902 FORD 7566

7566 JONES 7839

7839 KING

伪列level

select level,empno,ename,mgr from emp

start with (ename='SMITH')

connect by prior mgr=empno;

LEVEL EMPNO ENAME MGR

----- ---------- ---------- ----------

1 7369 SMITH 7902

2 7902 FORD 7566

3 7566 JONES 7839

4 7839 KING

从树根开始查询

select level,empno,ename,mgr from emp

start with (ename='KING')

connect by prior empno=mgr;

LEVEL EMPNO ENAME MGR

----- ---------- ---------- ----------

1 7839 KING

2 7566 JONES 7839

3 7902 FORD 7566

4 7369 SMITH 7902

2 7698 BLAKE 7839

3 7499 ALLEN 7698

3 7521 WARD 7698

3 7654 MARTIN 7698

3 7844 TURNER 7698

3 7900 JAMES 7698

2 7782 CLARK 7839

3 7934 MILLER 7782

美化层次关系

select lpad('-',level,'-')||ename from emp

start with (ename='KING')

connect by prior empno=mgr;

LPAD('-',LEVEL,'-')||ENAME

--------------------------------

-KING

--JONES

---FORD

----SMITH

--BLAKE

---ALLEN

---WARD

---MARTIN

---TURNER

---JAMES

--CLARK

---MILLER

删除节点，下级保留

select level,empno,ename,mgr from emp

where ename<>'BLAKE'

start with (ename='KING')

connect by prior empno=mgr;

BLAKE一个人删除，不影响他的下属

删除枝干

select level,empno,ename,mgr from emp

start with (ename='KING')

connect by prior empno=mgr

and ename<>'BLAKE';

BLAKE和他的整个部门