一、由根开始遍历树形结构(top->bottom)。
SQL> select employee_id,
2 manager_id,
3 level,
4 lpad(' ', (level - 1) * 3) || last_name || ', ' || first_name full_name
5 from hr.employees
6 start with manager_id is null
7 connect by manager_id = prior employee_id;
EMPLOYEE_ID MANAGER_ID LEVEL FULL_NAME
----------- ---------- ---------- --------------------------------------------------------------------------------
100 1 King, Steven
101 100 2 Kochhar, Neena
108 101 3 Greenberg, Nancy
109 108 4 Faviet, Daniel
110 108 4 Chen, John
111 108 4 Sciarra, Ismael
112 108 4 Urman, Jose Manuel
113 108 4 Popp, Luis
200 101 3 Whalen, Jennifer
203 101 3 Mavris, Susan
204 101 3 Baer, Hermann
205 101 3 Higgins, Shelley
206 205 4 Gietz, William
102 100 2 De Haan, Lex
103 102 3 Hunold, Alexander
104 103 4 Ernst, Bruce
105 103 4 Austin, David
106 103 4 Pataballa, Valli
107 103 4 Lorentz, Diana
114 100 2 Raphaely, Den
二、如果需要排序请使用order siblings by字句。
select employee_id,
level,
lpad(' ', (level - 1) * 3) || last_name || ', ' || first_name full_name
from hr.employees
start with manager_id is null
connect by manager_id = prior employee_id
order siblings by last_name, first_name;
三、熟悉使用sys_connect_by_path函数操作。
SQL> select '/u01/empl' ||
2 sys_connect_by_path(lower(last_name) || '.' || lower(first_name),'/') mgmt_path
3 from hr.employees
4 start with manager_id is null
5 connect by prior employee_id = manager_id;
MGMT_PATH
--------------------------------------------------------------------------------
/u01/empl/king.steven
/u01/empl/king.steven/kochhar.neena
/u01/empl/king.steven/kochhar.neena/greenberg.nancy
/u01/empl/king.steven/kochhar.neena/greenberg.nancy/faviet.daniel
/u01/empl/king.steven/kochhar.neena/greenberg.nancy/chen.john
/u01/empl/king.steven/kochhar.neena/greenberg.nancy/sciarra.ismael
/u01/empl/king.steven/kochhar.neena/greenberg.nancy/urman.jose manuel
四、遍历的同时指出是否是叶子节点（connect_by_isleaf函数）。
select lpad(' ', (level - 1) * 3) || last_name || ', ' || first_name full_name,
level,
connect_by_isleaf is_leaf
from hr.employees
start with manager_id is null
connect by prior employee_id = manager_id;
五、避免重复的循环（connect_by_iscycle 函数）。
select employee_id,
manager_id,
level lvl,
connect_by_iscycle is_cycle,
lpad(' ', (level - 1) * 3) || last_name || ', ' || first_name full_name
from hr.employees
start with last_name = 'Kochhar'
connect by nocycle manager_id = prior employee_id;
六、得到一个序列。
select level new_pk from dual connect by level <= 1000;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7204674/viewspace-621995/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Oracle的lob字段操作联系
下一篇：
数据库管理常用sql语句
请登录后发表评论
登录
全部评论
