3. Family tree syllability: select column from table_name start with column = valueconnect by prior parent key = child key
3.1 Exclude Singleness and Branch Take the EMP Table in Oracle as an Example [Example] List the information from each employee from top to bottom SQL> SELECT LPAD ('', 4 * (Level-1)) || Ename Name, Empno, Mgr from Emp Start with Mgr Is Null 2 Connect By Prior Empno = Mgr;
Name Empno Mgr ------------------------- King 7839 Jones 7566 7839 Scott 7788 7566 Adams 7876 7788
3.2 Trailed to the root [Example 1] Now starts from an employee to list the hierarchy of the employee SQL> COL ENAME for A30; SQL> SELECT LPAD ('', 4 * (Level-1)) | Ename Ename, Mgr, Empno from Scott.emp 2 Start with Mgr = 7788 Connect by Prior Mgr = Empno; ENAME MGR EMPNO ----------------------- --------------- --------- Adams 7788 7876 Scott 7566 7788 Jones 7839 7566 King 7839
[Example 2] List all employees hierarchy SQL> SELECT LPAD ('', 4 * (Level-1)) || Ename Ename, Empno, Mgr from Scott.emp 2 Start With Mgr Is Not Null 3 Connect By Empno = prior mgr;
