Data recursive query traversal SQL statements in Oracle

Query traversal, which needs to be stored in a hierarchical structure of data in the base table. For example, an organization is a typical example of this:

Implementation statement:

　

Select column

From table_name

Start with Column=value

Connect by prior Parent PRIMARY KEY = child foreign key

Example 1: In Oracle's EMP table, each record has a MGR column that uniquely identifies the current employee's empno and identifies the employee's manager. If Mgr is empty, the employee is the top of the organization. Now you want to list the hierarchy of each employee (from top to bottom):

　

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

Rows selected.

Sql>

　　

As can be seen from the query results, because Jones, Blake, Clark's boss is king, so Jones and other Mgr (manager number) =king's empno, that King's direct subordinates are Jones, Blake, Clark, Because their MGR is the same as King's empno.