[20150907]树形查询.txt
-- 昨天看一条sql语句，尝试优化，发现使用分析函数也不好，好像符合树形查询，最后觉得不妥，暂时放弃看看。
-- 但是还是学习树形查询的内容。最好的例子是scott.emp表。
-- 我是经常不记语法的人，或者记性不好。我总觉得理解很重要：
--例子：
select * from emp start with mgr is null connect by prior empno = mgr;
--理解： 从 mgr is null , 也就是从最高领导者开始，connect by 条件 相当于理解为前面的empno = 当前的 mgr。
--以上这条相当于从根节点向子节点查询。
--还有一个简单的记忆方法，prior empno 表示向雇员方向探查。
--写一个相反的例子,从子节点向根节点查询。prior mgr 表示向领导者方向探查。
SCOTT@test> select * from emp start with empno=7900 connect by prior mgr = empno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
--还有两个伪列CONNECT_BY_ISLEAF,CONNECT_BY_ISCYCLE.
SCOTT@test> select CONNECT_BY_ISLEAF,CONNECT_BY_ISCYCLE,emp.* from emp start with mgr is null connect by NOCYCLE prior empno = mgr;
CONNECT_BY_ISLEAF CONNECT_BY_ISCYCLE EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------------- ------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
0 0 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
0 0 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
0 0 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
1 0 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
0 0 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
1 0 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
0 0 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
1 0 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
1 0 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
1 0 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
1 0 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
1 0 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
0 0 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
1 0 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
-- CONNECT_BY_ISLEAF 表示是否是叶子节点(1=叶子 root以及分支=0),CONNECT_BY_ISCYCLE 表示是否存在环(0-不存在 1-存在)。
--如果查询根节点呢？ 使用函数CONNECT_BY_ROOT
SCOTT@test> select CONNECT_BY_ROOT(empno),CONNECT_BY_ISLEAF,CONNECT_BY_ISCYCLE,emp.* from emp start with mgr is null connect by NOCYCLE prior empno = mgr;
CONNECT_BY_ROOT(EMPNO) CONNECT_BY_ISLEAF CONNECT_BY_ISCYCLE EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------------------- ----------------- ------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7839 0 0 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7839 0 0 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7839 0 0 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 1 0 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7839 0 0 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7839 1 0 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7839 0 0 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7839 1 0 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7839 1 0 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7839 1 0 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7839 1 0 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7839 1 0 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7839 0 0 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 1 0 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
SCOTT@test> select CONNECT_BY_ROOT(empno),emp.* from emp start with empno=7900 connect by prior mgr = empno;
CONNECT_BY_ROOT(EMPNO) EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7900 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7900 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7900 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
--还有一个函数SYS_CONNECT_BY_PATH。
SCOTT@test> select emp.*,SYS_CONNECT_BY_PATH(empno,',') c80 from emp start with mgr is null connect by NOCYCLE prior empno = mgr;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO C80
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ---------------------------
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 ,7839
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 ,7839,7566
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 ,7839,7566,7788
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 ,7839,7566,7788,7876
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 ,7839,7566,7902
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 ,7839,7566,7902,7369
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 ,7839,7698
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 ,7839,7698,7499
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 ,7839,7698,7521
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 ,7839,7698,7654
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 ,7839,7698,7844
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 ,7839,7698,7900
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 ,7839,7782
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 ,7839,7782,7934
14 rows selected.
--记住这些基本差不多掌握树形查询了。
SCOTT@test> select * from (select substr(SYS_CONNECT_BY_PATH(dname,','),2) c50 ,CONNECT_BY_ISLEAF x1 from dept start with deptno=10 connect by prior deptno+10=deptno ) where x1=1;
C50 X1
-------------------------------------------------- ----------
ACCOUNTING,RESEARCH,SALES,OPERATIONS,MARKETING 1
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1793402/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-1793402/
扫一扫
392
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
