父取子，包含父
select * from tb_menu m start with m.id=1 connect by m.parent=prior m.id;
父取子，不包含父
select * from tb_menu m start with m.parent=1 connect by m.parent=prior m.id;
子取父，包含子
select * from tb_menu m start with m.id=38 connect by prior m.parent=m.id;
子取父，不包含子
select * from tb_menu m where m.ID<> 38 start with m.id=38 connect by prior m.parent=m.id;
--列转行
WM_CONCAT(ENT_ID)
—获取跟信息
connect_by_root()
--菜单目录结构表
create table tb_menu(
id number(10) not null, --主键id
title varchar2(50), --标题
parent number(10) --parent id
);
--父菜单
insert into tb_menu(id, title, parent) values(1, '父菜单1',null);
insert into tb_menu(id, title, parent) values(2, '父菜单2',null);
insert into tb_menu(id, title, parent) values(3, '父菜单3',null);
insert into tb_menu(id, title, parent) values(4, '父菜单4',null);
insert into tb_menu(id, title, parent) values(5, '父菜单5',null);
--一级菜单
insert into tb_menu(id, title, parent) values(6, '一级菜单6',1);
insert into tb_menu(id, title, parent) values(7, '一级菜单7',1);
insert into tb_menu(id, title, parent) values(8, '一级菜单8',1);
insert into tb_menu(id, title, parent) values(9, '一级菜单9',2);
insert into tb_menu(id, title, parent) values(10, '一级菜单10',2);
insert into tb_menu(id, title, parent) values(11, '一级菜单11',2);
insert into tb_menu(id, title, parent) values(12, '一级菜单12',3);
insert into tb_menu(id, title, parent) values(13, '一级菜单13',3);
insert into tb_menu(id, title, parent) values(14, '一级菜单14',3);
insert into tb_menu(id, title, parent) values(15, '一级菜单15',4);
insert into tb_menu(id, title, parent) values(16, '一级菜单16',4);
insert into tb_menu(id, title, parent) values(17, '一级菜单17',4);
insert into tb_menu(id, title, parent) values(18, '一级菜单18',5);
insert into tb_menu(id, title, parent) values(19, '一级菜单19',5);
insert into tb_menu(id, title, parent) values(20, '一级菜单20',5);
--二级菜单
insert into tb_menu(id, title, parent) values(21, '二级菜单21',6);
insert into tb_menu(id, title, parent) values(22, '二级菜单22',6);
insert into tb_menu(id, title, parent) values(23, '二级菜单23',7);
insert into tb_menu(id, title, parent) values(24, '二级菜单24',7);
insert into tb_menu(id, title, parent) values(25, '二级菜单25',8);
insert into tb_menu(id, title, parent) values(26, '二级菜单26',9);
insert into tb_menu(id, title, parent) values(27, '二级菜单27',10);
insert into tb_menu(id, title, parent) values(28, '二级菜单28',11);
insert into tb_menu(id, title, parent) values(29, '二级菜单29',12);
insert into tb_menu(id, title, parent) values(30, '二级菜单30',13);
insert into tb_menu(id, title, parent) values(31, '二级菜单31',14);
insert into tb_menu(id, title, parent) values(32, '二级菜单32',15);
insert into tb_menu(id, title, parent) values(33, '二级菜单33',16);
insert into tb_menu(id, title, parent) values(34, '二级菜单34',17);
insert into tb_menu(id, title, parent) values(35, '二级菜单35',18);
insert into tb_menu(id, title, parent) values(36, '二级菜单36',19);
insert into tb_menu(id, title, parent) values(37, '二级菜单37',20);
--三级菜单
insert into tb_menu(id, title, parent) values(38, '三级菜单38',21);
insert into tb_menu(id, title, parent) values(39, '三级菜单39',22);
insert into tb_menu(id, title, parent) values(40, '三级菜单40',23);
insert into tb_menu(id, title, parent) values(41, '三级菜单41',24);
insert into tb_menu(id, title, parent) values(42, '三级菜单42',25);
insert into tb_menu(id, title, parent) values(43, '三级菜单43',26);
insert into tb_menu(id, title, parent) values(44, '三级菜单44',27);
insert into tb_menu(id, title, parent) values(45, '三级菜单45',28);
insert into tb_menu(id, title, parent) values(46, '三级菜单46',28);
insert into tb_menu(id, title, parent) values(47, '三级菜单47',29);
insert into tb_menu(id, title, parent) values(48, '三级菜单48',30);
insert into tb_menu(id, title, parent) values(49, '三级菜单49',31);
insert into tb_menu(id, title, parent) values(50, '三级菜单50',31);
commit;
select * from tb_menu;
SQL> col ename for a30
SQL> select empno,lpad('',level*2-1,' ')||ename ename,job,mgr,deptno,level
from emp
start with mgr is null
2 3 4 connect by prior empno=mgr;
EMPNO ENAME JOB MGR DEPTNO LEVEL
---------- ------------------------------ --------- ---------- ---------- ----------
7839 KING PRESIDENT 10 1
7566 JONES MANAGER 7839 20 2
7788 SCOTT ANALYST 7566 20 3
7876 ADAMS CLERK 7788 20 4
7902 FORD ANALYST 7566 20 3
7369 SMITH CLERK 7902 20 4
7698 BLAKE MANAGER 7839 30 2
7499 ALLEN SALESMAN 7698 30 3
7521 WARD SALESMAN 7698 30 3
7654 MARTIN SALESMAN 7698 30 3
7844 TURNER SALESMAN 7698 30 3
7900 JAMES CLERK 7698 30 3
7782 CLARK MANAGER 7839 10 2
7934 MILLER CLERK 7782 10 3
14 rows selected.
SQL> select empno,lpad(' ',level*2-1,' ')||ename ename,job,mgr,deptno,level
2 from emp
start with mgr is null
connect by prior empno=mgr;
3 4
EMPNO ENAME JOB MGR DEPTNO LEVEL
---------- ------------------------------ --------- ---------- ---------- ----------
7839 KING PRESIDENT 10 1
7566 JONES MANAGER 7839 20 2
7788 SCOTT ANALYST 7566 20 3
7876 ADAMS CLERK 7788 20 4
7902 FORD ANALYST 7566 20 3
7369 SMITH CLERK 7902 20 4
7698 BLAKE MANAGER 7839 30 2
7499 ALLEN SALESMAN 7698 30 3
7521 WARD SALESMAN 7698 30 3
7654 MARTIN SALESMAN 7698 30 3
7844 TURNER SALESMAN 7698 30 3
7900 JAMES CLERK 7698 30 3
7782 CLARK MANAGER 7839 10 2
7934 MILLER CLERK 7782 10 3
14 rows selected.
SQL> select empno,lpad(' ',level*2-1,' ')||ename ename,job,mgr,deptno,level
2 from emp
3 start with mgr is null
4 connect by prior empno=mgr
5 order siblings by emp.ename;
EMPNO ENAME JOB MGR DEPTNO LEVEL
---------- ------------------------------ --------- ---------- ---------- ----------
7839 KING PRESIDENT 10 1
7698 BLAKE MANAGER 7839 30 2
7499 ALLEN SALESMAN 7698 30 3
7900 JAMES CLERK 7698 30 3
7654 MARTIN SALESMAN 7698 30 3
7844 TURNER SALESMAN 7698 30 3
7521 WARD SALESMAN 7698 30 3
7782 CLARK MANAGER 7839 10 2
7934 MILLER CLERK 7782 10 3
7566 JONES MANAGER 7839 20 2
7902 FORD ANALYST 7566 20 3
7369 SMITH CLERK 7902 20 4
7788 SCOTT ANALYST 7566 20 3
7876 ADAMS CLERK 7788 20 4
14 rows selected.
##这里注意，如果要在层级关系中排序，使用order siblings by这样，可以明确层级关系。
SQL> select empno,lpad(' ',level*2-1,' ')||ename ename,job,mgr,deptno,level
2 from emp
3 start with mgr is null
4 connect by prior empno=mgr
5 order by ename;
EMPNO ENAME JOB MGR DEPTNO LEVEL
---------- ------------------------------ --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 20 4
7369 SMITH CLERK 7902 20 4
7499 ALLEN SALESMAN 7698 30 3
7902 FORD ANALYST 7566 20 3
7900 JAMES CLERK 7698 30 3
7654 MARTIN SALESMAN 7698 30 3
7934 MILLER CLERK 7782 10 3
7788 SCOTT ANALYST 7566 20 3
7844 TURNER SALESMAN 7698 30 3
7521 WARD SALESMAN 7698 30 3
7698 BLAKE MANAGER 7839 30 2
7782 CLARK MANAGER 7839 10 2
7566 JONES MANAGER 7839 20 2
7839 KING PRESIDENT 10 1
14 rows selected.
SQL> select empno,lpad(' ',level*2-1,' ')||ename ename,job,mgr,deptno,level
2 from emp
3 start with mgr is null
4 connect by prior empno=mgr
5 order by emp.ename;
EMPNO ENAME JOB MGR DEPTNO LEVEL
---------- ------------------------------ --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 20 4
7499 ALLEN SALESMAN 7698 30 3
7698 BLAKE MANAGER 7839 30 2
7782 CLARK MANAGER 7839 10 2
7902 FORD ANALYST 7566 20 3
7900 JAMES CLERK 7698 30 3
7566 JONES MANAGER 7839 20 2
7839 KING PRESIDENT 10 1
7654 MARTIN SALESMAN 7698 30 3
7934 MILLER CLERK 7782 10 3
7788 SCOTT ANALYST 7566 20 3
7369 SMITH CLERK 7902 20 4
7844 TURNER SALESMAN 7698 30 3
7521 WARD SALESMAN 7698 30 3
14 rows selected.
SQL> set pagesize 10000
SQL> select empno,lpad(' ',level*2-1,' ')||ename ename,mgr,deptno,sys_connect_by_path(empno,'/') c1, sys_connect_by_path(ename,'/') c2,connect_by_isleaf isleaf
2 from emp
3 start with mgr is null
4 connect by prior empno=mgr;
EMPNO ENAME MGR DEPTNO C1 C2 ISLEAF
---------- -------------------- ---------- ---------- -------------------------------------------------- -------------------------------------------------- ----------
7839 KING 10 /7839 /KING 0
7566 JONES 7839 20 /7839/7566 /KING/JONES 0
7788 SCOTT 7566 20 /7839/7566/7788 /KING/JONES/SCOTT 0
7876 ADAMS 7788 20 /7839/7566/7788/7876 /KING/JONES/SCOTT/ADAMS 1
7902 FORD 7566 20 /7839/7566/7902 /KING/JONES/FORD 0
7369 SMITH 7902 20 /7839/7566/7902/7369 /KING/JONES/FORD/SMITH 1
7698 BLAKE 7839 30 /7839/7698 /KING/BLAKE 0
7499 ALLEN 7698 30 /7839/7698/7499 /KING/BLAKE/ALLEN 1
7521 WARD 7698 30 /7839/7698/7521 /KING/BLAKE/WARD 1
7654 MARTIN 7698 30 /7839/7698/7654 /KING/BLAKE/MARTIN 1
7844 TURNER 7698 30 /7839/7698/7844 /KING/BLAKE/TURNER 1
7900 JAMES 7698 30 /7839/7698/7900 /KING/BLAKE/JAMES 1
7782 CLARK 7839 10 /7839/7782 /KING/CLARK 0
7934 MILLER 7782 10 /7839/7782/7934 /KING/CLARK/MILLER 1
14 rows selected.
select empno,lpad(' ',level*2-1,' ')||ename ename,mgr,deptno,sys_connect_by_path(empno,'/') c1, sys_connect_by_path(ename,'/') c2,connect_by_isleaf isleaf,connect_by_root(ename),connect_by_root(empno)
from emp
start with mgr is null
connect by prior empno=mgr;
SQL> select empno,lpad(' ',level*2-1,' ')||ename ename,mgr,deptno,sys_connect_by_path(empno,'/') c1, sys_connect_by_path(ename,'/') c2,connect_by_isleaf isleaf,connect_by_root(ename),connect_by_root(empno)
2 from emp
3 start with mgr is null
4 connect by prior empno=mgr;
EMPNO ENAME MGR DEPTNO C1 C2 ISLEAF CONNECT_BY CONNECT_BY_ROOT(EMPNO)
---------- -------------------- ---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------------------
7839 KING 10 /7839 /KING 0 KING 7839
7566 JONES 7839 20 /7839/7566 /KING/JONES 0 KING 7839
7788 SCOTT 7566 20 /7839/7566/7788 /KING/JONES/SCOTT 0 KING 7839
7876 ADAMS 7788 20 /7839/7566/7788/7876 /KING/JONES/SCOTT/ADAMS 1 KING 7839
7902 FORD 7566 20 /7839/7566/7902 /KING/JONES/FORD 0 KING 7839
7369 SMITH 7902 20 /7839/7566/7902/7369 /KING/JONES/FORD/SMITH 1 KING 7839
7698 BLAKE 7839 30 /7839/7698 /KING/BLAKE 0 KING 7839
7499 ALLEN 7698 30 /7839/7698/7499 /KING/BLAKE/ALLEN 1 KING 7839
7521 WARD 7698 30 /7839/7698/7521 /KING/BLAKE/WARD 1 KING 7839
7654 MARTIN 7698 30 /7839/7698/7654 /KING/BLAKE/MARTIN 1 KING 7839
7844 TURNER 7698 30 /7839/7698/7844 /KING/BLAKE/TURNER 1 KING 7839
7900 JAMES 7698 30 /7839/7698/7900 /KING/BLAKE/JAMES 1 KING 7839
7782 CLARK 7839 10 /7839/7782 /KING/CLARK 0 KING 7839
7934 MILLER 7782 10 /7839/7782/7934 /KING/CLARK/MILLER 1 KING 7839
14 rows selected.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28572479/viewspace-2140441/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/28572479/viewspace-2140441/
扫一扫
299
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
