Oracle hierarchical query statement

Oracle provides an interesting connect by clause, which can sort the branches with a family tree structure. It applies to the structure of organizations or companies, financial account code, and so on.

To use query traversal, you need to store data in the base table according to the hierarchy. For example, an organizational unit is such a typical example:

Implementation statement:

SELECT column

FROM table_name

Start with column = value

Connect by prior parent primary key = Child foreign key

Example 1: In the oracle emp table, each record has an empno column that uniquely identifies the current employee and the mgr column that identifies the manager of the employee. If mgr is empty, the employee is the highest level in the organization. Www.bkjia.com ):

Select lpad ('', 4 * (level-1) | ename name, empno, mgr from emp

Start with mgr is null

Connect by prior empno = mgr;

NAME EMPNO MGR

--------------------------------------

KING 7839

JONES 7566 7839

SCOTT 7788 7566

ADAMS 7876 7788

FORD 7902 7566

SMITH 7369 7902

BLAKE 7698 7839

ALLEN 7499 7698

WARD 7521 7698

MARTIN 7654 7698

TURNER 7844 7698

JAMES 7900 7698

CLARK 7782 7839

MILLER 7934 7782

14 rows selected.

SQL>

From the query results, we can see that because JONES, BLAKE, and CLARK have KING superiors, JONES and so on MGR (Manager number) = KING's empno, that is, the direct subordinates of KING are JONES, BLAKE, and CLARK, because their MGR is the same as KING's EMPNO.