Oracle SQL Tree Operations

Syntax: Select...start with...connect By...prior

There are two main points

1) The prior is placed on the child node side, indicating that the scan tree is scanned from top to bottom as the node specified by start with the root node. May correspond to one or more branches.

Start with can be omitted if omitted, indicating that all nodes are traversed as root nodes, respectively

Select Deptid,deptname,dept_pid,dept_pname, leader,remark,deptdesc,sortsno,status,levelid from T_SYS_DEPT1
START with Deptid=#{treeroot} CONNECT by PRIOR deptid = Dept_pid;------------------------------------(root----> Leaf)

2) The prior is placed on the parent node, which means that the scan tree is scanned from bottom to top with the node specified in start with as the lowest layer child node. The order is that the child nodes are scanned toward the parent node until the root node, which can only get one branch.

Start with can be omitted, and if omitted, indicates that all nodes are traversed as the lowest level child nodes toward the root node respectively

Select Deptid,levelid from T_SYS_DEPT1
START with Deptid=#{id} CONNECT by PRIOR dept_pid = deptid;---------------------------------(leaf----> root)

Oracle SQL Tree Operations