Oracle Connect By Function:
[url]http://www.psoug.org/reference/connectby.html[/url]
START WITH and CONNECT BY in Oracle SQL
[url]http://www.adp-gmbh.ch/ora/sql/connect_by.html[/url]
How the start with CONNECT BY clause in Oracle works
[url]http://www.oradev.com/connect_by.jsp[/url]
about level:
[url]http://www.adp-gmbh.ch/ora/sql/level.html[/url]
[quote]level is a pseudo column that can be used in hierarchical queries (start with .. connect by).
For records that appear in the root, level is 1, for their (direct) children, level is 2 and so on.[/quote]
LEVEL是伪列,用来表示该条记录位于树形结构的第几层
START WITH 代表你要开始遍历的的节点
CONNECT BY PRIOR 是标示父子关系的对应
[b]START WITH and CONNECT BY in Oracle[/b]
[url]http://www.compshack.com/sql/start-with-and-connect-by-oracle-sql[/url]
[quote]
Here is a quick example to help you understand how oracle Start With analytical function works. The start with .. connect by clause can be used to select data that has a hierarchical relationship. Usually, it is some sort of parent child relationship like supervisor and an employee.
Let’s assume we have a table called employees. I would like to know employees that directly report to supervisor Id 1122456 –simple, right?
Now I would like to know all employees that report to supervisor Id 1122456 (directly on indirectly).
Notice that with the 1st sql we had 3 rows total while using Start With gave us 4 rows back. If you look at the last row on the 2nd select, you notice that employee 0120033 reports to 0120022 who in return reports to 1122456.
[/quote]
[url]http://www.psoug.org/reference/connectby.html[/url]
START WITH and CONNECT BY in Oracle SQL
[url]http://www.adp-gmbh.ch/ora/sql/connect_by.html[/url]
How the start with CONNECT BY clause in Oracle works
[url]http://www.oradev.com/connect_by.jsp[/url]
about level:
[url]http://www.adp-gmbh.ch/ora/sql/level.html[/url]
[quote]level is a pseudo column that can be used in hierarchical queries (start with .. connect by).
For records that appear in the root, level is 1, for their (direct) children, level is 2 and so on.[/quote]
LEVEL是伪列,用来表示该条记录位于树形结构的第几层
START WITH 代表你要开始遍历的的节点
CONNECT BY PRIOR 是标示父子关系的对应
[b]START WITH and CONNECT BY in Oracle[/b]
[url]http://www.compshack.com/sql/start-with-and-connect-by-oracle-sql[/url]
[quote]
Here is a quick example to help you understand how oracle Start With analytical function works. The start with .. connect by clause can be used to select data that has a hierarchical relationship. Usually, it is some sort of parent child relationship like supervisor and an employee.
Let’s assume we have a table called employees. I would like to know employees that directly report to supervisor Id 1122456 –simple, right?
SELECT emplid, supervisor_id
FROM ps_employees
WHERE supervisor_id = '1122456';
EMPLID SUPERVISOR_ID
0119676 1122456
0112356 1122456
0120022 1122456
Now I would like to know all employees that report to supervisor Id 1122456 (directly on indirectly).
SELECT emplid, supervisor_id
FROM ps_employees
START WITH supervisor_id = '1122456'
CONNECT BY PRIOR emplid = supervisor_id;
EMPLID SUPERVISOR_ID
0119676 1122456
0112356 1122456
0120022 1122456
0120033 0120022
Notice that with the 1st sql we had 3 rows total while using Start With gave us 4 rows back. If you look at the last row on the 2nd select, you notice that employee 0120033 reports to 0120022 who in return reports to 1122456.
[/quote]