Oracle Hierarchy Queries

1 Definition:

           Hierarchical query using tree traversal, go through the data collection with tree structure, to get the tree hierarchical Relationship report method           Tree-structured parent-child relationship, you can control the direction of:         ① traversal tree, top-down, or bottom-up          ②  to determine the beginning of the hierarchy Point (Root) location           Hierarchical query statements are determined from these two aspects, start with OK start point, connect by determines the direction of traversal  www.2cto.com & nbsp;           2 syntax:            Comment:      &NBSP ;  ①level is a pseudo-column, which indicates that levels          ②from can only be a single table or view, and that view cannot contain join    & nbsp The    ③where condition restricts the rows returned by the query, but does not affect the hierarchical relationship, which belongs to truncating the node, but the lower child of the truncated node is not affected          ④ Prior is an adjective that can be placed anywhere          ⑤ The complete pruning condition should be placed on connect by, and the single-point cut-off condition should be placed in the WHERE clause. However, the priority of connect by is higher than where, that is, the SQL engine first performs connect by         ⑥ in start with the expression can have subqueries, but the Connect By cannot have subqueries            3 calendar tree:     &NBSP;    ㈠start with clause           Start with determines which row to use as root, and if there is no Start with, each row is treated as root and then its descendants are found, which does not is a real query. Start with can use a subquery or any valid conditional expression           Example: [Sql] select level,id,manager_id,last_name, Title from S_emp        start with title= (select title from s_emp where manager_id is null)  &nbs P     Connect by prior id=manager_id;            ㈡connect by clause           Connect by and prior determine the bar of a hierarchical query and traverse direction (prior OK)           Connect by prior column_1=column_2;          its The prior indicates the meaning of the previous node, before and after the Connect by equals column, or before the column in select  www.2cto.com            Connect by can also bring multiple conditions, such as connect by prior id=manager_id and id>10               &N Bsp         1. ) Top-down traversal:              &NBSP         first the root node and then the child nodes. Column_1 indicates that the parent key,column_2 represents a child key. In this case: Connect by prior parent Key= child key represents top-down, equivalent to connect by child Key=prior parent key.              &NB Sp         Example: [Sql] select level,employee_id,manager_id,last_name,job_id from S_emp        start with manager_id=100        Connect by  employee_id=prior manager_id;                           &NBSP;2. ) Bottom-up traversal:                          first by the bottommost sub-node, the traversal always finds the root node. Contrary to the above. Connect by cannot have subqueries, but other conditions can be added, such as adding and ID!=2. This sentence will truncate the branches, if there are many descendants below this node of id=2, then all truncation does not appear.                           Example: [Sql] select Level,emplo yee_id,manager_id,last_name,job_id from S_emp        start with manager_id=100    www.2cto . com        Connect by prior employee_id=manager_id and employee_id<>120;             4 use level and Lpad format reports:          levels are a pseudo-column of hierarchical queries, if there are Level, must have connect By,start with can not           Lpad is to add a certain length of character to the left of a string, and satisfies the intermediate parameter length requirements, does not meet the automatic addition           Example: [Sql] select level,employee_id,manager_id,lpad (Last_name,length (last_name ) + (level*4) -4, ' _ '), job_id from S_emp        start with manager_id=100        Conne CT by prior employee_id=manager_id and employee_id<>120             5 Trimming branches: The           WHERE clause deletes the node, but its descendants are not affected, and the add condition in connect by will delete the entire tree that satisfies the condition, including the descendants. Note that if you add the condition to the root after connect by, the result is the same as no add            6 actual application           1) Query the number of nodes on each level [sql]   see a total of several levels:  select count (distinct level) &NBSP;&NBSP;FROm s_emp   start with manager_id are null   connect by prior employee_id=manager_id    to view each such The number of nodes on the level, as long as the level of grouping, and statistics of nodes, you can write:  select level,count (last_name)   from s_emp   start with MANAGER_ID is null   connect by prior employee_id=manager_id   group by level   &NBSP;WWW.2CTO.C Om             2) View level relationships           For example given a specific employee to see if there is administrative authority on an employee [SQL]  select level,a.* from   s_emp a  where first_name= ' Douglas '--managed node  start with MANAGER_ID is n Ull--Start node, that is: root node  connect by prior employee_id=manager_id              3) Delete child Tree             For example, there is a need to lay off the staff, including the managers of a department.             All employees managed by the ID 2 employee include themselves delete [Sql] delete from S_emp where employee_id in ( elect employee_id from   s_emp a  start with employee_id=2-Employees from id=2Start looking for its child nodes, remove the entire tree  connect by prior employee_id=manager_id)               4) Find the manager of each department [Sql] select level,a.* from    s_emp a    www.2cto.com   start with MANAGER_ID is null   connect by prior employee_id=manager_id and department_id!=prior department_id;-- The dept_id of the current row is not equal to the dept_id of the previous row, that is, each subtree selects the highest level node              5) to query the highest rank in an organization [SQL]  select level,a.* from    s_emp a    where level <=2– find top two levels   start with Mana GER_ID is null   connect by prior employee_id=manager_id and department_id!=prior department_id;          6) Total level           There are two requirements, one is to do an accumulative calculation salary a specified subtree subtree, One is to use each row as the root node, and then add the compute salary to all child nodes that belong to the node. [sql]       The first is very simple, so sum is fine, the statement:  select sum (Salary) from    s_emp a    start with id=2-e.g. starting from id=2   connect by Prior id=manager_id;          2nd requirement, the 1th is required, the cumulative value of the tree is calculated for each root node, and the start node of the internal level query is obtained from the outer query.  select last_name,salary, (   select sum (Salary) from    s_emp    www.2cto.com   start with id=a.id– let each node become root   connect by prior id=manager_id) Sumsalary   from s_ EMP A;              7) find leaf nodes in the specified hierarchy            leaf (leaf) is an isolated node without descendants. Oracle 10g provides a simple connect_by_isleaf=1,0 representation of non-leaf nodes [Sql] select level,id,manager_id,last_name, title from S_emp      where connect_by_isleaf=1– means query leaf node        start with  manager_id=2 &NBSP;&N Bsp     Connect by prior id=manager_id;             7 10g new features:          ① using siblings keyword sorting   & nbsp           If you use order by sorting to break the hierarchy, in oracle10g, the sort of siblings keyword is added &nbSp             Syntax: Order  siblings  by <expre>              It will protect levels and sort by expre in each level               Examples: [Sql] select level,   & nbsp;www.2cto.com         employee_id,last_name,manager_id          From S_emp         start with manager_id are null         connect by Prio R employee_id=manager_id         order siblings by Last_Name;            ②CONNECT_BY_ROOT              oracle10g New Connect_by_root, the value of the same column name that represents the root node of this row before the column name               Example: [Sql] select connect_ By_root last_name root_last_name, Connect_by_root employee_id root_id,        Employee_id,last_name , manager_id        from S_emp       Start with manager_id are null        Connect by prior employee_id=manager_id