迭代查詢 樹形結構及常用的函式
父取子，包含父
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 聊聊mysql的樹形結構儲存及查詢MySql
- 樹形結構的儲存與查詢
- Oracle 樹形結構查詢的特殊用法Oracle
- 樹形結構
- layui樹形結構UI
- java樹形結構Java
- 樹形結構的選單表設計與查詢
- 利用樹形查詢實現部分分析函式功能函式
- 遞迴函式-樹形列表遞迴函式
- 樹形結構處理
- LayUI—tree樹形結構的使用UI
- 七、基本資料結構（樹形結構）資料結構
- fstat函式及struct stat結構函式Struct
- markdown樹形結構生成工具
- 關於樹結構的查詢優化，及許可權樹的查詢優化優化
- php tree類的使用（樹形結構）PHP
- [Swing]樹形結構的實現
- 一個簡單的樹形結構
- MySql樹形結構（多級選單）查詢設計方案MySql
- 資料結構中樹形結構簡介資料結構
- Hive常用函式及自定義函式Hive函式
- ORACLE常用結構和函式使用方法總結Oracle函式
- 樹形查詢
- 工具函式：普通陣列如何轉為樹形結構資料(多層級)陣列？函式陣列
- Linux 下樹形結構的檢視Linux
- 總結常用的字串函式字串函式
- Linux用樹形結構顯示目錄結構Linux
- oracle樹形查詢Oracle
- 字串陣列轉為樹形結構字串陣列
- 我做的一個挺拙劣樹形結構
- 樹形結構的處理——組合模式（五）模式
- 樹形結構的處理——組合模式（四）模式
- 樹形結構的處理——組合模式（三）模式
- 樹形結構的處理——組合模式（二）模式
- 樹形結構的處理——組合模式（一）模式
- PHP常用函式總結PHP函式
- Oracle常用函式總結Oracle函式
- php 常用函式總結PHP函式