【Oracle】层次查询 connect by prior start with 子句用法无限层级无限递归次数递归遍历树形数据记录表

向右看齐 2022-04-01 04:42 454阅读 0赞

Oracle SqlServer Mysql 各自的 无限级 次数 递归 遍历树 结构记录数据 参考下面文章：

[https://blog.csdn.net/xuheng8600/article/details/85848660][https_blog.csdn.net_xuheng8600_article_details_85848660]

Oracle SqlServer Mysql 无限级 次数 递归 遍历树 Oracle start with...connect by prior， with as 递归 ，存储过程递归

先看Oracle官方文档：

[https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Queries.html\#GUID-E3D35EF7-33C3-4D88-81B3-00030C47AE56][https_docs.oracle.com_en_database_oracle_oracle-database_18_sqlrf_Hierarchical-Queries.html_GUID-E3D35EF7-33C3-4D88-81B3-00030C47AE56]

1.  [Home][]
2.  [Database][]
3.  [Oracle][]
4.  [Oracle Database][]
5.  [Release 18][]

# SQL Language Reference #

## Hierarchical Queries (SQL层次查询) ##

If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause:

hierarchical\_query\_clause::=

![Description of hierarchical\_query\_clause.eps follows][Description of hierarchical_query_clause.eps follows]  
[Description of the illustration hierarchical\_query\_clause.eps][Description of the illustration hierarchical_query_clause.eps]

`condition` can be any condition as described in [Conditions][].

`START` `WITH` specifies the root row(s) of the hierarchy.

`CONNECT` `BY` specifies the relationship between parent rows and child rows of the hierarchy.

*  The `NOCYCLE` parameter instructs Oracle Database to return rows from a query even if a `CONNECT` `BY` loop exists in the data. Use this parameter along with the `CONNECT_BY_ISCYCLE` pseudocolumn to see which rows contain the loop. Refer to [CONNECT\_BY\_ISCYCLE Pseudocolumn][CONNECT_BY_ISCYCLE Pseudocolumn] for more information.
 *  In a hierarchical query, one expression in `condition` must be qualified with the `PRIOR` operator to refer to the parent row. For example,
    
        ... PRIOR expr = expr
        or
        ... expr = PRIOR expr
    
    If the `CONNECT` `BY` `condition` is compound, then only one condition requires the `PRIOR` operator, although you can have multiple `PRIOR` conditions. For example:
    
        CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
        CONNECT BY PRIOR employee_id = manager_id and 
                   PRIOR account_mgr_id = customer_id ...
    
    `PRIOR` is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
    
    `PRIOR` is most commonly used when comparing column values with the equality operator. (The `PRIOR` keyword can be on either side of the operator.) `PRIOR` causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in `CONNECT` `BY` clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.

Both the `CONNECT` `BY` condition and the `PRIOR` expression can take the form of an uncorrelated subquery. However, `CURRVAL` and `NEXTVAL` are not valid `PRIOR` expressions, so the `PRIOR` expression cannot refer to a sequence.

You can further refine a hierarchical query by using the `CONNECT_BY_ROOT` operator to qualify a column in the select list. This operator extends the functionality of the `CONNECT` `BY` \[`PRIOR`\] condition of hierarchical queries by returning not only the immediate parent row but all ancestor rows in the hierarchy.

See Also:

[CONNECT\_BY\_ROOT][CONNECT_BY_ROOT] for more information about this operator and "[Hierarchical Query Examples][https_docs.oracle.com_en_database_oracle_oracle-database_18_sqlrf_Hierarchical-Queries.html_GUID-E3D35EF7-33C3-4D88-81B3-00030C47AE56]"

Oracle processes hierarchical queries as follows:

*  A join, if present, is evaluated first, whether the join is specified in the `FROM` clause or with `WHERE` clause predicates.
 *  The `CONNECT` `BY` condition is evaluated.
 *  Any remaining `WHERE` clause predicates are evaluated.

Oracle then uses the information from these evaluations to form the hierarchy using the following steps:

1.  Oracle selects the root row(s) of the hierarchy—those rows that satisfy the `START` `WITH` condition.
2.  Oracle selects the child rows of each root row. Each child row must satisfy the condition of the `CONNECT` `BY` condition with respect to one of the root rows.
3.  Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step [2][], and then the children of those children, and so on. Oracle always selects children by evaluating the `CONNECT` `BY` condition with respect to a current parent row.
4.  If the query contains a `WHERE` clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the `WHERE` clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.
5.  Oracle returns the rows in the order shown in [Figure 9-1][]. In the diagram, children appear below their parents. For an explanation of hierarchical trees, see [Figure 3-1][].

Figure 9-1 Hierarchical Queries

![Description of Figure 9-1 follows][]  
[Description of "Figure 9-1 Hierarchical Queries"][Description of _Figure 9-1 Hierarchical Queries]

To find the children of a parent row, Oracle evaluates the `PRIOR` expression of the `CONNECT` `BY` condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The `CONNECT` `BY`condition can contain other conditions to further filter the rows selected by the query.

If the `CONNECT` `BY` condition results in a loop in the hierarchy, then Oracle returns an error. A loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendent) of another row.

Note:

In a hierarchical query, do not specify either `ORDER` `BY` or `GROUP` `BY`, as they will override the hierarchical order of the `CONNECT` `BY` results. If you want to order rows of siblings of the same parent, then use the `ORDER` `SIBLINGS` `BY` clause. See [order\_by\_clause][order_by_clause].

### Hierarchical Query Examples ###

CONNECT BY Example

The following hierarchical query uses the `CONNECT` `BY` clause to define the relationship between employees and managers:

SELECT employee_id, last_name, manager_id
       FROM employees
       CONNECT BY PRIOR employee_id = manager_id;
    
    EMPLOYEE_ID LAST_NAME                 MANAGER_ID
    ----------- ------------------------- ----------
            101 Kochhar                          100
            108 Greenberg                        101
            109 Faviet                           108
            110 Chen                             108
            111 Sciarra                          108
            112 Urman                            108
            113 Popp                             108
            200 Whalen                           101
            203 Mavris                           101
            204 Baer                             101
    . . .

LEVEL Example

The next example is similar to the preceding example, but uses the `LEVEL` pseudocolumn to show parent and child rows:

SELECT employee_id, last_name, manager_id, LEVEL
       FROM employees
       CONNECT BY PRIOR employee_id = manager_id;
    
    EMPLOYEE_ID LAST_NAME                 MANAGER_ID      LEVEL
    ----------- ------------------------- ---------- ----------
            101 Kochhar                          100          1
            108 Greenberg                        101          2
            109 Faviet                           108          3
            110 Chen                             108          3
            111 Sciarra                          108          3
            112 Urman                            108          3
            113 Popp                             108          3
            200 Whalen                           101          2
            203 Mavris                           101          2
            204 Baer                             101          2
            205 Higgins                          101          2
            206 Gietz                            205          3
            102 De Haan                          100          1
    ...

START WITH Examples

The next example adds a `START` `WITH` clause to specify a root row for the hierarchy and an `ORDER` `BY` clause using the `SIBLINGS`keyword to preserve ordering within the hierarchy:

SELECT last_name, employee_id, manager_id, LEVEL
          FROM employees
          START WITH employee_id = 100
          CONNECT BY PRIOR employee_id = manager_id
          ORDER SIBLINGS BY last_name;
    
    LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
    ------------------------- ----------- ---------- ----------
    King                              100                     1
    Cambrault                         148        100          2
    Bates                             172        148          3
    Bloom                             169        148          3
    Fox                               170        148          3
    Kumar                             173        148          3
    Ozer                              168        148          3
    Smith                             171        148          3
    De Haan                           102        100          2
    Hunold                            103        102          3
    Austin                            105        103          4
    Ernst                             104        103          4
    Lorentz                           107        103          4
    Pataballa                         106        103          4
    Errazuriz                         147        100          2
    Ande                              166        147          3
    Banda                             167        147          3
    ...

In the `hr.employees` table, the employee Steven King is the head of the company and has no manager. Among his employees is John Russell, who is the manager of department 80. If you update the `employees` table to set Russell as King's manager, you create a loop in the data:

UPDATE employees SET manager_id = 145
 WHERE employee_id = 100;
 
 SELECT last_name "Employee", 
 LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
 FROM employees
 WHERE level <= 3 AND department_id = 80
 START WITH last_name = 'King'
 CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;
 
 ERROR:
 ORA-01436: CONNECT BY loop in user data

The `NOCYCLE` parameter in the `CONNECT` `BY` condition causes Oracle to return the rows in spite of the loop. The `CONNECT_BY_ISCYCLE` pseudocolumn shows you which rows contain the cycle:

SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
 LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
 FROM employees
 WHERE level <= 3 AND department_id = 80
 START WITH last_name = 'King'
 CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4
 ORDER BY "Employee", "Cycle", LEVEL, "Path";
 
 Employee Cycle LEVEL Path
 ------------------------- ---------- ---------- -------------------------
 Abel 0 3 /King/Zlotkey/Abel
 Ande 0 3 /King/Errazuriz/Ande
 Banda 0 3 /King/Errazuriz/Banda
 Bates 0 3 /King/Cambrault/Bates
 Bernstein 0 3 /King/Russell/Bernstein
 Bloom 0 3 /King/Cambrault/Bloom
 Cambrault 0 2 /King/Cambrault
 Cambrault 0 3 /King/Russell/Cambrault
 Doran 0 3 /King/Partners/Doran
 Errazuriz 0 2 /King/Errazuriz
 Fox 0 3 /King/Cambrault/Fox
 ...

CONNECT\_BY\_ISLEAF Example

The following statement shows how you can use a hierarchical query to turn the values in a column into a comma-delimited list:

SELECT LTRIM(SYS_CONNECT_BY_PATH (warehouse_id,','),',') FROM
       (SELECT ROWNUM r, warehouse_id FROM warehouses)
       WHERE CONNECT_BY_ISLEAF = 1
       START WITH r = 1
       CONNECT BY r = PRIOR r + 1
       ORDER BY warehouse_id; 
     
    LTRIM(SYS_CONNECT_BY_PATH(WAREHOUSE_ID,','),',')
    --------------------------------------------------------------------------------
    1,2,3,4,5,6,7,8,9

CONNECT\_BY\_ROOT Examples

The following example returns the last name of each employee in department 110, each manager at the highest level above that employee in the hierarchy, the number of levels between manager and employee, and the path between the two:

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
       LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
       FROM employees
       WHERE LEVEL > 1 and department_id = 110
       CONNECT BY PRIOR employee_id = manager_id
       ORDER BY "Employee", "Manager", "Pathlen", "Path";
    
    Employee        Manager            Pathlen Path
    --------------- --------------- ---------- ------------------------------
    Gietz           Higgins                  1 /Higgins/Gietz
    Gietz           King                     3 /King/Kochhar/Higgins/Gietz
    Gietz           Kochhar                  2 /Kochhar/Higgins/Gietz
    Higgins         King                     2 /King/Kochhar/Higgins
    Higgins         Kochhar                  1 /Kochhar/Higgins

The following example uses a `GROUP` `BY` clause to return the total salary of each employee in department 110 and all employees above that employee in the hierarchy:

SELECT name, SUM(salary) "Total_Salary" FROM (
       SELECT CONNECT_BY_ROOT last_name as name, Salary
          FROM employees
          WHERE department_id = 110
          CONNECT BY PRIOR employee_id = manager_id)
          GROUP BY name
       ORDER BY name, "Total_Salary";
    
    NAME                      Total_Salary
    ------------------------- ------------
    Gietz                             8300
    Higgins                          20300
    King                             20300
    Kochhar                          20300

See Also:

*  [LEVEL Pseudocolumn][] and [CONNECT\_BY\_ISCYCLE Pseudocolumn][CONNECT_BY_ISCYCLE Pseudocolumn] for a discussion of how these pseudocolumns operate in a hierarchical query
 *  [SYS\_CONNECT\_BY\_PATH][SYS_CONNECT_BY_PATH] for information on retrieving the path of column values from root to node
 *  [order\_by\_clause][order_by_clause] for more information on the `SIBLINGS` keyword of `ORDER` `BY` clauses
 *  [subquery\_factoring\_clause][subquery_factoring_clause], which supports recursive subquery factoring (recursive WITH) and lets you query hierarchical data. This feature is more powerful than `CONNECT` `BY` in that it provides depth-first search and breadth-first search, and supports multiple recursive branches.

*  [Previous][]
 *  [Next][]

[https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Query-Pseudocolumns.html\#GUID-D91FFF59-ECB0-40F0-AB4C-7A9D27EBEEF1][LEVEL Pseudocolumn]

## Hierarchical Query Pseudocolumns (层次查询伪列) ##

The hierarchical query pseudocolumns are valid only in hierarchical queries. The hierarchical query pseudocolumns are:

*  [CONNECT\_BY\_ISCYCLE Pseudocolumn][CONNECT_BY_ISCYCLE Pseudocolumn]
 *  [CONNECT\_BY\_ISLEAF Pseudocolumn][CONNECT_BY_ISLEAF Pseudocolumn]
 *  [LEVEL Pseudocolumn][]

To define a hierarchical relationship in a query, you must use the `CONNECT` `BY` clause.

### CONNECT\_BY\_ISCYCLE Pseudocolumn ###

The `CONNECT_BY_ISCYCLE` pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.

You can specify `CONNECT_BY_ISCYCLE` only if you have specified the `NOCYCLE` parameter of the `CONNECT` `BY` clause. `NOCYCLE` enables Oracle to return the results of a query that would otherwise fail because of a `CONNECT` `BY` loop in the data.

See Also:

[Hierarchical Queries][] for more information about the `NOCYCLE` parameter and [Hierarchical Query Examples][https_docs.oracle.com_en_database_oracle_oracle-database_18_sqlrf_Hierarchical-Queries.html_GUID-E3D35EF7-33C3-4D88-81B3-00030C47AE56] for an example that uses the `CONNECT_BY_ISCYCLE` pseudocolumn

### CONNECT\_BY\_ISLEAF Pseudocolumn ###

The `CONNECT_BY_ISLEAF` pseudocolumn returns 1 if the current row is a leaf of the tree defined by the `CONNECT` `BY` condition. Otherwise it returns 0. This information indicates whether a given row can be further expanded to show more of the hierarchy.

CONNECT\_BY\_ISLEAF Example

The following example shows the first three levels of the `hr.employees` table, indicating for each row whether it is a leaf row (indicated by 1 in the `IsLeaf` column) or whether it has child rows (indicated by 0 in the `IsLeaf` column):

SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf",
 LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
 FROM employees
 WHERE LEVEL <= 3 AND department_id = 80
 START WITH employee_id = 100
 CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4
 ORDER BY "Employee", "IsLeaf";
 
 Employee IsLeaf LEVEL Path
 ------------------------- ---------- ---------- -------------------------
 Abel 1 3 /King/Zlotkey/Abel
 Ande 1 3 /King/Errazuriz/Ande
 Banda 1 3 /King/Errazuriz/Banda
 Bates 1 3 /King/Cambrault/Bates
 Bernstein 1 3 /King/Russell/Bernstein
 Bloom 1 3 /King/Cambrault/Bloom
 Cambrault 0 2 /King/Cambrault
 Cambrault 1 3 /King/Russell/Cambrault
 Doran 1 3 /King/Partners/Doran
 Errazuriz 0 2 /King/Errazuriz
 Fox 1 3 /King/Cambrault/Fox
 . . .

See Also:

[Hierarchical Queries][] and [SYS\_CONNECT\_BY\_PATH][SYS_CONNECT_BY_PATH]

### LEVEL Pseudocolumn ###

For each row returned by a hierarchical query, the `LEVEL` pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children. A leaf row is any row without children. [Figure 3-1][] shows the nodes of an inverted tree with their `LEVEL` values.

Figure 3-1 Hierarchical Tree

![Description of Figure 3-1 follows][]  
[Description of "Figure 3-1 Hierarchical Tree "][Description of _Figure 3-1 Hierarchical Tree]

See Also:

[Hierarchical Queries][] for information on hierarchical queries in general and [IN Condition][] for restrictions on using the `LEVEL`pseudocolumn

*  [Previous][Previous 1]
 *  [Next][Next 1]

[https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Query-Pseudocolumns.html\#GUID-D91FFF59-ECB0-40F0-AB4C-7A9D27EBEEF1][LEVEL Pseudocolumn]

[https://blog.csdn.net/u013766398/article/details/53285327][https_blog.csdn.net_u013766398_article_details_53285327]

转

# Oracle start with connect by prior 用法（递归查询树形结构） #

2016年11月22日 14:13:18 [风灵寒][Link 1] 阅读数：461

语法：

select \* from  表名 where  条件1 start with 条件2 connect by prior 当前表字段=级联表字段。

start with与connect by prior语句完成递归记录，形成一棵树形结构，通常可以在具有层次结构的表中使用。

start with：表示开始的记录

connect by prior：指定与当前记录关联时的字段关系

代码：--

创建部门表，这是一个具有层次结构的表，子记录通过parent\_id与父记录的id进行关联

create table DEPT(

ID NUMBER(9) PRIMARY KEY       --部门ID

NAME VARCHAR2(100),             --部门名称

PARENT\_ID NUMBER(9) --父级部门ID，通过此字段与上级部门关联

);

向表中插入如下数据，为了使代码简单，一个部门仅具有一个下级部门

从根节点开始查询递归的记录

select \* from dept  start with id=1 connect by prior id = parent\_id;

下面是查询结果，

start with id=1  表示从id=1的记录开始查询，向叶子的方向递归，递归条件是id=parent\_id，当前记录的id等于子记录的parent\_id

●从叶子节点开始查询递归的记录

select \* from dept start with id=5 connect by prior parent\_id = id;

下面是查询结果，递归条件按照当前记录的parent\_id等与父记录的id

●对查询结果过滤

select \* from dept where name like '%销售%' start with id=1 connect by prior id = parent\_id;

在下面的查询结果中可以看到，首先使用start with... connect by prior查询出树形的结构，然后where条件才生效，对全部查询结果进行过滤

●prior的作用

prior关键字表示不进行递归查询，仅查询出满足id=1的记录，下面是将第一个查询去掉prior关键字后结果

select \* from dept start with id=1 connect by prior id = parent\_id

**注：**select connect\_by\_root 列名  from 表名 where条件1 start with 条件2 connect by prior 当前表字段=级联表字段。

表示查找父级(父亲)的直系子级(儿子)

http://blog.csdn.net/u013492963/article/details/18551701

[https://blog.csdn.net/u013766398/article/details/53285327][https_blog.csdn.net_u013766398_article_details_53285327]

[https://blog.csdn.net/feier7501/article/details/21815691][https_blog.csdn.net_feier7501_article_details_21815691]

原

# oracle 递归查询 CONNECT BY、START WITH、CONNECT\_BY\_ROOT、CONNECT\_BY\_ISLEAF、SYS\_CONNECT\_BY\_PATH #

2014年03月22日 18:21:37 [feier7501][] 阅读数：5985更多

个人分类： [oracle][]

创建表，初始化数据;

1.  `CREATE TABLE TB_COMPANY`
2.  `(`
3.  `COMPANY_ID INTEGER PRIMARY KEY,`
4.  `COMPANY VARCHAR2(256),`
5.  `UP_COMPANYID INTEGER`
6.  `);`
7.   
8.  `INSERT INTO TB_COMPANY VALUES (0, '总公司', NULL);`
9.  `INSERT INTO TB_COMPANY VALUES (1, '北京分公司', 0);`
10. `INSERT INTO TB_COMPANY VALUES (2, '上海分公司', 0);`
11. `INSERT INTO TB_COMPANY VALUES (3, '海淀区分部', 1);`
12. `INSERT INTO TB_COMPANY VALUES (4, '东城区分部', 1);`
13. `INSERT INTO TB_COMPANY VALUES (5, '黄埔区分部', 2);`
14. `INSERT INTO TB_COMPANY VALUES (6, '静安区分部', 2);`
15. `COMMIT;`

全部数据：

![Center][]

递归查询SQL：

1.  `SELECT (RPAD(' ', 2*(LEVEL-1), '-' ) || COMPANY) COMPANY_NAME, CONNECT_BY_ROOT COMPANY, CONNECT_BY_ISLEAF, LEVEL , SYS_CONNECT_BY_PATH(COMPANY, '/')`
2.  `FROM TB_COMPANY`
3.  `START WITH UP_COMPANYID IS NULL`
4.  `CONNECT BY PRIOR COMPANY_ID = UP_COMPANYID;`

结果：

![Center 1][]

说明：

1. CONNECT\_BY\_ROOT 返回当前节点的最顶端节点   
2. CONNECT\_BY\_ISLEAF 判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点   
3. LEVEL 伪列表示节点深度   
4. SYS\_CONNECT\_BY\_PATH函数显示详细路径，并用“/”分隔

递归查询SQL：

SELECT * FROM TB_COMPANY START WITH COMPANY_ID = 1 CONNECT BY PRIOR COMPANY_ID = UP_COMPANYID;

结果：

![Center 2][]

[https://blog.csdn.net/feier7501/article/details/21815691][https_blog.csdn.net_feier7501_article_details_21815691]

[https://blog.csdn.net/guaoran/article/details/52918372][https_blog.csdn.net_guaoran_article_details_52918372]

原

# 利用start with connect by 查询子节点和父节点数据显示为树状形 #

2016年10月25日 09:27:11 [guaoran][] 阅读数：2322 标签： [Oracle使用sql查询出树状型][Oracle_sql][根据子节点和父节点查询出树状型][Link 2][start with connect b][] 更多

个人分类： [oracle][oracle 1]

**利用start with   connect by  查询子节点和父节点数据显示为树状形**

前段时间做了一个根据子节点和父节点查询出树状型

表数据：

![Center 3][]

要实现的结果:

![Center 4][]

实现的sql：

1.  `select rpad('---', (level - 1) * 3, '---') || name as name, id`
2.  `from t_nscreen_region`
3.  `start with id = 1`
4.  `connect by nocycle prior id = parent_id`

order SIBLINGS BY id;

其中函数理解：

1.PRIOR 
       阶层查询的CONNECY BY condition的条件式需要用到PRIOR来指定父节点
    2.CONNECT BY
       通过CONNECT BY子句定义子节点和父节点的关系
    3.start with 
       通过start with 指定根节点（不指定start with会出现重复记，不指定NOCYCLE没事）
    4.LEVEL 
       通过LEVEL虚拟列表示节点的关系
    5.rpad(string,padded_length,[pad_string])函数
       从右边对字符串使用指定的字符进行填充
       string:表示要追加的字符，
       padded_length:表示追加后的长度
       pad_string：表示string字符长度不够padded_length时，取pad_string的字符，默认为空格
    
    6.lpad( string1, padded_length, [ pad_string ] )函数
       从左边对字符串使用指定的字符进行填充
       string1：源字符串
       padded_length:最终返回的字符串的长度，如果最终返回的字符串的长度比源字符串的小，那么此函数实际上对源串进行截断处理
       pad_string:用于填充的字符，可以不填，默认为空字符
    7.order SIBLINGS BY id ：按id进行排序

[https://blog.csdn.net/guaoran/article/details/52918372][https_blog.csdn.net_guaoran_article_details_52918372]

[https://zhidao.baidu.com/question/752780874722290124.html][https_zhidao.baidu.com_question_752780874722290124.html]

# oracle 递归查询 能限定递归次数么 #

oracle递归查询  
Oracle中start by prior子句用法  
connect by 是结构化查询中用到的，其基本语法是：  
select ... from tablename start with 条件1  
connect by 条件2  
where 条件3;  
  
例：  
select \* from table  
start with org\_id = 'HBHqfWGWPy'  
connect by prior org\_id = parent\_id;  
  
简单说来是将一个[树状结构][Link 3]存储在一张表里，比如一个表中存在两个字段:  
org\_id,parent\_id那么通过表示每一条记录的parent是谁，就可以形成一个[树状结构][Link 3]。  
用上述语法的查询可以取得这棵树的所有记录。  
其中：  
  
条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。  
条件2 是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org\_id = parent\_id就是说上一条记录的org\_id 是本条记录的parent\_id，即本记录的父亲是上一条记录。  
条件3 是过滤条件，用于对返回的所有记录进行过滤。  
  
简单介绍如下：  
  
早扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：  
第一步：从根节点开始；  
第二步：访问该节点；  
第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；  
第四步：若该节点为根节点，则访问完毕，否则执行第五步；  
第五步：返回到该节点的父节点，并执行第三步骤。  
  
总之：扫描整个树结构的过程也即是[中序遍历][Link 4]树的过程。  
  
1． 树结构的描述  
树结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如EMP表中的EMPNO和MGR。EMPNO表示该雇员的编号，MGR表示领导该雇员的人的编号，即子节点的MGR值等于父节点的EMPNO值。在表的每一行中都有一个表示父节点的MGR（除根节点外），通过每个节点的父节点，就可以确定整个树结构。  
在SELECT命令中使用CONNECT BY 和蔼START WITH 子句可以查询表中的树型结构关系。其命令格式如下：  
SELECT 。。。  
CONNECT BY \{PRIOR 列名1=列名2|列名1=PRIOR 裂名2\}  
\[START WITH\]；  
其中：CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是[自顶向下][Link 5]还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。  
  
START WITH： 不但可以指定一个根节点，还可以指定多个根节点。  
2． 关于PRIOR  
运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。  
PRIOR被置于CONNECT BY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为[自顶向下][Link 5]的方式。如：  
CONNECT BY PRIOR EMPNO=MGR  
PIROR运算符被置于CONNECT BY 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：  
CONNECT BY EMPNO=PRIOR MGR  
在这种方式中也应指定一个开始的节点。  
3． 定义查找起始节点  
在[自顶向下][Link 5]查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。  
  
4．使用LEVEL  
在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。图1.2就表示了树结构的层次。  
  
5．节点和分支的裁剪  
在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。  
6．排序显示  
象在其它查询中一样，在树结构查询中也可以使用ORDER BY 子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序。

本回答由提问者推荐

[https://zhidao.baidu.com/question/752780874722290124.html][https_zhidao.baidu.com_question_752780874722290124.html]

[![返回主页][logo.gif]][logo.gif 1]

# [Debugs][logo.gif 1] #

# #

*  [博客园][Link 6]
 *  [首页][logo.gif 1]
 *  [新随笔][Link 7]
 *  [联系][Link 8]
 *  [订阅][Link 9]
 *  [管理][Link 10]

随笔 - 152  文章 - 1  评论 - 0

# [oracle的递归运算(树运算) 无限树形][oracle_] #

oracle的递归运算(树运算)start with org\_id ='1'connect by prior parent\_id=son\_id

1.前言   
  oracle的递归运算，在我们web页面的目录结构中会经常用到，据说是面试经常出的题目，而网上的一些东西说得都不是很明了，所以自己整理了一下,以下主要通过一个例子来说明，可以直接copy代码运行一下。   
  www.2cto.com    
2.start with org\_id = '条件1'  prior parent\_id =  son\_id; 的作用   
  这个就是为了把树形结构全部查出来，树的目录就放在同一张表中,如   
1   
|--2   
|--3   
|--4   
   |--5   
      |--6   
|--7   
   |--8   
      |--9   
         |--10   
  www.2cto.com    
这样的结构怎么查出来呢，这种特殊的查询，connect by perior就派上用场了 。   
3.代码   
Java代码    
create table TESTTEMP    
(    
  parent\_ID    VARCHAR2(30),    
  son\_ID VARCHAR2(30)    
);    
    
insert into TESTTEMP values('1','0');    
insert into TESTTEMP values('1','2');    
insert into TESTTEMP values('1','3');    
insert into TESTTEMP values('1','4');    
insert into TESTTEMP values('1','7');    
insert into TESTTEMP values('4','5');    
insert into TESTTEMP values('5','6');    
insert into TESTTEMP values('7','8');    
insert into TESTTEMP values('8','9');    
insert into TESTTEMP values('9','10');    
commit;    
select \* from TESTTEMP     
start with parent\_ID='1'    
connect  by  parent\_ID= prior son\_ID     
  www.2cto.com    
结果:   
1 0   
1 2   
1 3   
1 4   
4 5   
5 6   
1 7   
7 8   
8 9   
9 10   
4。总结   
  上面的例子，作用是实现遍历所有节点。如果prior 放在 connect  by后面的话，那就是从上查找

分类: [Oracle][Oracle 1]

好文要顶 关注我 收藏该文 ![icon_weibo_24.png][] ![wechat.png][]

[![sample_face.gif][]][sample_face.gif 1]

[Debugs][sample_face.gif 1]  
[关注 - 0][- 0]  
[粉丝 - 0][- 0 1]

\+加关注

[« ][Link 11]上一篇：[oracle递归查询子节点][Link 11]  
[» ][Link 12]下一篇：[使用jQuery.noConflict方法来轻松实现控制权的转交][Link 12]

posted @ 2017-08-22 12:40 [Debugs][logo.gif 1] 阅读(372) 评论(0) [编辑][Link 13] [收藏][Link 14]

刷新评论[刷新页面][Link 14][返回顶部][Link 15]

[/images/20220401/f67654c1a4154c0888a30eda7e8f1f83.pngp/7411037.html][oracle_]

[https://blog.csdn.net/jackpk/article/details/3247549][https_blog.csdn.net_jackpk_article_details_3247549]

原

# oracle中进行简单树查询(递归查询) ，PRIOR、CONNECT\_BY\_ROOT的使用 #

2008年11月07日 16:05:00 [jackpk][] 阅读数：11211 标签： [oracle][oracle 2][manager][][path][][up][][user][][数据库][Link 16] 更多

个人分类： [oracle][oracle 3]

<table style="margin-left:auto;"> 
 <tbody> 
 <tr> 
 <td style="text-align:left;vertical-align:top;width:492.7pt;"> select&nbsp;AD_DEPARTMENT_CODE, AD_DEPARTMENT_UP_LEVEL, AD_DEPARTMENT_NAME &nbsp;&nbsp;from&nbsp;(select&nbsp;AD_DEPARTMENT_CODE, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AD_DEPARTMENT_UP_LEVEL, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AD_DEPARTMENT_NAME &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from&nbsp;AD_DEPT_ARCHITECTURE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;order&nbsp;by&nbsp;AD_DEPARTMENT_UP_LEVEL, AD_DEPARTMENT_CODE) &nbsp;start with&nbsp;AD_DEPARTMENT_CODE =&nbsp;'0' connect by prior&nbsp;AD_DEPARTMENT_CODE = AD_DEPARTMENT_UP_LEVEL; 
 <table style="margin-left:auto;"> 
 <tbody> 
 <tr> 
 <td style="text-align:left;vertical-align:top;width:481.15pt;"> 对from&nbsp;后边&nbsp;的select&nbsp;结果集合进行了排序，然后，start with&nbsp;、connect by prior这些语句起了作用，按照刚才查询出来结果集的顺序， （1）&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;先找到AD_DEPARTMENT_CODE =&nbsp;'0'的作为第一条记录(A)，将其加入到要构建的树状结构中。 （2）&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;然后顺序从排完序的结果集中找出，第一条记录的头一个子结点(B)添加到树中。如第一个子结点还有它的子结点，就依次从刚才的查询出来的结果集中找到B的子节点C1、C2、C3。。。依次添加到树中。 （3）&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;。。。。 &nbsp;有了from&nbsp;后的select结果集的排序，得到的树状结构更加有序。 </td> 
 </tr> 
 </tbody> 
 </table>&nbsp; </td> 
 </tr> 
 <tr> 
 <td style="text-align:left;vertical-align:top;width:492.7pt;"> 
 <ul style="margin-left:0px;"> 
 <li> 生成媒体频道栏目父子关系树状关系 </li> 
 </ul>SELECT&nbsp;APR_CODE, APR_CODE_UP, NAMES &nbsp;&nbsp;FROM&nbsp;(SELECT&nbsp;A.MEDIA_CODE ||&nbsp;'|'&nbsp;|| A.CHANNEL_CODE ||&nbsp;'|'&nbsp;|| A.COLUMN_CODE&nbsp;AS&nbsp;APR_CODE, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A.MEDIA_CODE_UP ||&nbsp;'|'&nbsp;|| A.CHANNEL_CODE_UP ||&nbsp;'|'&nbsp;|| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A.COLUMN_CODE_UP&nbsp;AS&nbsp;APR_CODE_UP, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DECODE((B.MEDIA_NAME ||&nbsp;'-'&nbsp;|| C.CHANNEL_CODE ||&nbsp;'-'&nbsp;|| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;D.COLUMN_NAME), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'--', &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'产品关系', &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(B.MEDIA_NAME ||&nbsp;'|'&nbsp;|| C.CHANNEL_CODE ||&nbsp;'|'&nbsp;|| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;D.COLUMN_NAME)) NAMES &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;COLUMNS&nbsp;D, CHANNEL C, MEDIA B, AD_PRODUCT_RELATION A &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE&nbsp;A.MEDIA_CODE = B.MEDIA_CODE(+) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;A.MEDIA_CODE = C.MEDIA_CODE(+) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;A.CHANNEL_CODE = C.CHANNEL_CODE(+) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;A.MEDIA_CODE = D.MEDIA_CODE(+) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;A.CHANNEL_CODE = D.CHANNEL_CODE(+) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;A.COLUMN_CODE = D.COLUMN_CODE(+)) &nbsp;START&nbsp;WITH&nbsp;APR_CODE =&nbsp;'0|0|0' CONNECT&nbsp;BY&nbsp;PRIOR&nbsp;APR_CODE = APR_CODE_UP &nbsp; 这里利用'-'&nbsp;和'|'来进行区分，找出了树状结构的根 为了构建树， （1）先建立了树结构的根，字段这里都设为“0”或者“00” （2）然后再根据需要填充数据 （3）利用上边的oracle的&nbsp;PRIOR，CONNECT_BY_ROOT&nbsp;等进行层次查询 &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="text-align:left;vertical-align:top;width:492.7pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="text-align:left;vertical-align:top;width:492.7pt;"> 
 <ul style="margin-left:0px;"> 
 <li> 递归查询时：由于数据类型不正确而导致“ORA-01436: CONNECT BY loop in user data” </li> 
 </ul>错误原因：在数据库表TRASFER中，TRASFER字段为number类型，而TAPE_NO字段为Varchar2类型。由于在进行父子关系判定中“TRANSFER = TAPE_NO”两者的字段类型不同而导致：ORA-01436 &nbsp; 正确语句： SELECT&nbsp;TRANSFER, TAPE_NO, TRANSFER_NAME &nbsp;&nbsp;FROM&nbsp;(SELECT&nbsp;TRANSFER, TAPE_NO, TRANSFER_NAME &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;TRANSFER &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ORDER&nbsp;BY&nbsp;TAPE_NO, TRANSFER) &nbsp;START&nbsp;WITH&nbsp;TRANSFER =&nbsp;0 CONNECT&nbsp;BY&nbsp;PRIOR&nbsp;TO_CHAR(TRANSFER)&nbsp;= TAPE_NO </td> 
 </tr> 
 <tr> 
 <td style="text-align:left;vertical-align:top;width:492.7pt;"> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

参考一

<table style="margin-left:auto;"> 
 <tbody> 
 <tr> 
 <td style="text-align:left;vertical-align:top;width:492.7pt;"> PL/SQL基础：阶层查询 作者：itpub cxck&nbsp;&nbsp;2007-05-08 内容导航： &nbsp;ORACLE&nbsp;10g新增了阶层查询操作符PRIOR，CONNECT_BY_ROOT&nbsp; ■PRIOR&nbsp; 阶层查询的CONNECY BY condition的条件式需要用到PRIOR来指定父节点，&nbsp; 作为运算符，PRIOR和加(+)减(-)运算的优先级相同。&nbsp; ■阶层查询&nbsp; 语法：START WITH&nbsp;condition&nbsp;CONNECT BY&nbsp;NOCYCLE&nbsp;condition&nbsp; START WITH&nbsp;指定阶层的根&nbsp; CONNECT BY&nbsp;指定阶层的父/子关系&nbsp; NOCYCLE&nbsp;存在CONNECT BY LOOP的纪录时，也返回查询结果。&nbsp; condition ... PRIOR expr = expr&nbsp;或者&nbsp;... expr = PRIOR expr&nbsp; 例：&nbsp; CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...&nbsp; CONNECT BY PRIOR employee_id = manager_id and&nbsp; PRIOR account_mgr_id = customer_id ...&nbsp; ■CONNECT_BY_ROOT&nbsp; 查询指定根的阶层数据。&nbsp; ■CONNECT BY子句的例子&nbsp; 通过CONNECT BY子句定义职员和上司的关系。&nbsp; SQL&gt;SELECT employee_id, last_name, manager_id&nbsp; FROM employees&nbsp; CONNECT BY PRIOR employee_id = manager_id;&nbsp; EMPLOYEE_ID LAST_NAME MANAGER_ID&nbsp; ----------- ------------------------- ----------&nbsp; 101 Kochhar 100&nbsp; 108 Greenberg 101&nbsp; 109 Faviet 108&nbsp; 110 Chen 108&nbsp; 111 Sciarra 108&nbsp; 112 Urman 108&nbsp; 113 Popp 108&nbsp; 200 Whalen 101&nbsp; ■LEVEL的例子&nbsp; 通过LEVEL虚拟列表示节点的关系。&nbsp; SQL&gt;SELECT employee_id, last_name, manager_id, LEVEL&nbsp; FROM employees&nbsp; CONNECT BY PRIOR employee_id = manager_id;&nbsp; EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL&nbsp; ----------- ------------------------- ---------- ----------&nbsp; 101 Kochhar 100 1&nbsp; 108 Greenberg 101 2&nbsp; 109 Faviet 108 3&nbsp; 110 Chen 108 3&nbsp; 111 Sciarra 108 3&nbsp; 112 Urman 108 3&nbsp; 113 Popp 108 3&nbsp; ■START WITH子句的例子&nbsp; 通过START WITH指定根节点，ORDER SIBLINGS BY保持阶层的顺序。 SQL&gt;SELECT&nbsp;last_name, employee_id, manager_id,&nbsp;LEVEL FROM&nbsp;employees START&nbsp;WITH&nbsp;employee_id&nbsp;=&nbsp;100 CONNECT&nbsp;BY&nbsp;PRIOR employee_id&nbsp;=&nbsp;manager_id ORDER&nbsp;SIBLINGS&nbsp;BY&nbsp;last_name; LAST_NAME EMPLOYEE_ID MANAGER_ID&nbsp;LEVEL ------------------------- ----------- ---------- ---------- King&nbsp;100&nbsp;1 Cambrault&nbsp;148&nbsp;100&nbsp;2 Bates&nbsp;172&nbsp;148&nbsp;3 Bloom&nbsp;169&nbsp;148&nbsp;3 Fox&nbsp;170&nbsp;148&nbsp;3 Kumar&nbsp;173&nbsp;148&nbsp;3 Ozer&nbsp;168&nbsp;148&nbsp;3 Smith&nbsp;171&nbsp;148&nbsp;3 De Haan&nbsp;102&nbsp;100&nbsp;2 Hunold&nbsp;103&nbsp;102&nbsp;3 Austin&nbsp;105&nbsp;103&nbsp;4 Ernst&nbsp;104&nbsp;103&nbsp;4 Lorentz&nbsp;107&nbsp;103&nbsp;4 Pataballa&nbsp;106&nbsp;103&nbsp;4 Errazuriz&nbsp;147&nbsp;100&nbsp;2 Ande&nbsp;166&nbsp;147&nbsp;3 Banda&nbsp;167&nbsp;147&nbsp;3 &nbsp; hr.employees里，Steven King是公司的最高责任者，没有上司，他有一个叫John Russell的下属是部门80的管理者。&nbsp; 更新employees表，把Russell设置成King的上司，这样就产生了CONNECT BY LOOP。 SQL&gt;UPDATE&nbsp;employees&nbsp;SET&nbsp;manager_id&nbsp;=&nbsp;145 WHERE&nbsp;employee_id&nbsp;=&nbsp;100; SQL&gt;SELECT&nbsp;last_name "Employee", LEVEL, SYS_CONNECT_BY_PATH(last_name,&nbsp;'/') "Path" FROM&nbsp;employees WHERE&nbsp;level&nbsp;&lt;=&nbsp;3&nbsp;AND&nbsp;department_id&nbsp;=&nbsp;80 START&nbsp;WITH&nbsp;last_name&nbsp;=&nbsp;'King' CONNECT&nbsp;BY&nbsp;PRIOR employee_id&nbsp;=&nbsp;manager_id&nbsp;AND&nbsp;LEVEL&nbsp;&lt;=&nbsp;4; 2&nbsp;3&nbsp;4&nbsp;5&nbsp;6&nbsp;7&nbsp;ERROR: ORA-01436: CONNECT&nbsp;BY&nbsp;loop&nbsp;in&nbsp;user&nbsp;data CONNECT&nbsp;BY&nbsp;NOCYCLE强制返回查询结果。CONNECT_BY_ISCYCLE显示是否存在LOOP。 SQL&gt;SELECT&nbsp;last_name "Employee", CONNECT_BY_ISCYCLE "Cycle", LEVEL, SYS_CONNECT_BY_PATH(last_name,&nbsp;'/') "Path" FROM&nbsp;employees WHERE&nbsp;level&nbsp;&lt;=&nbsp;3&nbsp;AND&nbsp;department_id&nbsp;=&nbsp;80 START&nbsp;WITH&nbsp;last_name&nbsp;=&nbsp;'King' CONNECT&nbsp;BY&nbsp;NOCYCLE PRIOR employee_id&nbsp;=&nbsp;manager_id&nbsp;AND&nbsp;LEVEL&nbsp;&lt;=&nbsp;4; Employee Cycle&nbsp;LEVEL&nbsp;Path ------------------------- ------ ------ ------------------------- Russell&nbsp;1&nbsp;2&nbsp;/King/Russell Tucker&nbsp;0&nbsp;3&nbsp;/King/Russell/Tucker Bernstein&nbsp;0&nbsp;3&nbsp;/King/Russell/Bernstein Hall&nbsp;0&nbsp;3&nbsp;/King/Russell/Hall Olsen&nbsp;0&nbsp;3&nbsp;/King/Russell/Olsen Cambrault&nbsp;0&nbsp;3&nbsp;/King/Russell/Cambrault Tuvault&nbsp;0&nbsp;3&nbsp;/King/Russell/Tuvault Partners&nbsp;0&nbsp;2&nbsp;/King/Partners King&nbsp;0&nbsp;3&nbsp;/King/Partners/King Sully&nbsp;0&nbsp;3&nbsp;/King/Partners/Sully McEwen&nbsp;0&nbsp;3&nbsp;/King/Partners/McEwen &nbsp; ■CONNECT_BY_ROOT的例子&nbsp; 1，查询110部门的职员，上司，职员和上司之间级别差及路径。 SELECT&nbsp;last_name "Employee", CONNECT_BY_ROOT last_name "Manager", LEVEL-1&nbsp;"Pathlen", SYS_CONNECT_BY_PATH(last_name,&nbsp;'/') "Path" FROM&nbsp;employees WHERE&nbsp;LEVEL&nbsp;&gt;&nbsp;1&nbsp;and&nbsp;department_id&nbsp;=&nbsp;110 CONNECT&nbsp;BY&nbsp;PRIOR employee_id&nbsp;=&nbsp;manager_id; Employee Manager Pathlen Path --------------- ------------ ---------- ----------------------------------- Higgins Kochhar&nbsp;1&nbsp;/Kochhar/Higgins Gietz Kochhar&nbsp;2&nbsp;/Kochhar/Higgins/Gietz Gietz Higgins&nbsp;1&nbsp;/Higgins/Gietz Higgins King&nbsp;2&nbsp;/King/Kochhar/Higgins Gietz King&nbsp;3&nbsp;/King/Kochhar/Higgins/Gietz &nbsp; 2，使用GROUP BY语句，查询110部门的职员以及该职员下属职员的工资和。 SELECT&nbsp;name,&nbsp;SUM(salary) "Total_Salary"&nbsp;FROM&nbsp;( SELECT&nbsp;CONNECT_BY_ROOT last_name&nbsp;as&nbsp;name, Salary FROM&nbsp;employees WHERE&nbsp;department_id&nbsp;=&nbsp;110 CONNECT&nbsp;BY&nbsp;PRIOR employee_id&nbsp;=&nbsp;manager_id) GROUP&nbsp;BY&nbsp;name; NAME Total_Salary ------------------------- ------------ Gietz&nbsp;8300 Higgins&nbsp;20300 King&nbsp;20300 Kochhar&nbsp;20300 &nbsp; &nbsp; &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

参考二

[https://blog.csdn.net/jackpk/article/details/3247549][https_blog.csdn.net_jackpk_article_details_3247549]

[https://blog.csdn.net/m0\_37298602/article/details/77472942][https_blog.csdn.net_m0_37298602_article_details_77472942]

转

# Oracle 层次查询(递归查询)（level start with connect by prior） #

2017年08月22日 10:03:14 [King\_Kwin][King_Kwin] 阅读数：578

# #

## 声明：部分内容来源于网络资源，只为记录学习使用，如有侵权，请留言删除 ##

## 层次查询的概念 ##

**语法格式：**

**select \[level\], column, expr... from table  
\[where condition\]  
start with condition  
connect by \[prior column1= column2 |  
column1 = prior column2\];**

**层次查询是通过start with和connect by子句标识的：**

1.其中level关键字是**可选**的，表示等级，1表示root,2表示root的child,其他相同的规则。

2.From之后可以是table,view但是**只能是一个table。**

3.**Where条件限制了查询返回的行，但是不影响层次关系，属于将节点截断，但是这个被截断的节点的下层child不受影响。**

4.Start with是表示开始节点，对于一个真实的层次关系，必须要有这个子句，但是不是必须的。

5.connect by prior是指定父子关系，其中prior的位置不一定要在connect by之后，对于一个真实的层次关系，这也是必须的。

**对于from是视图的，那么这个view不能包含join。**

**层次查询限制**

1.层次查询from之后如果是table，只能是一个table，不能有join。

2.from之后如果是view，则view不能是带join的。

3.使用order by子句，order子句是在等级层次做完之后开始的，所以对于层次查询来说没有什么意义，除非特别关注level，获得某行在层次中的深度，但是这两种都会破坏层次。见增强特性中的使用siblings排序。

4.在start with中表达式可以有子查询，但是connect by中不能有子查询。

**层次查询的增强特性**

**1、SYS\_CONNECT\_BY\_PATH**

**[Oracle][Oracle 2] 9i**提供了**sys\_connect\_by\_path(column,char)**,其中column是**字符型或能自动转换成字符型**的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。**这个函数只能使用在层次查询中。**

**下面的是oracle10g新增特性**

**2、CONNECT\_BY\_ISLEAF**

**   **在oracle9i的时候，查找指定root下的叶子节点，是很复杂的，oracle10g引入了一个新的函数，connect\_by\_isleaf,如果行的值为0表示不是叶子节点，1表示是叶子节点。

**3、CONNECT\_BY\_ISCYCLE和NOCYCLE关键字**

**   **如果从root节点开始找其子孙，找到一行，结果发生和祖先互为子孙的情况，则发生循环，[oracle][Oracle 2]会报**ORA-01436: CONNECT BY loop in user data，**在9i中只能将发生死循环的不加入到树中或删除，在10g中可以用nocycle关键字加在connect by之后，避免循环的参加查询操作。并且通过connect\_by\_iscycle得到哪个节点发生循环。0表示未发生循环，1表示发生了循环。

**4、CONNECT\_BY\_ROOT**

**   **Oracle10g新增connect\_by\_root,用在列名之前表示此行的根节点的相同列名的值。

**5、使用SIBLINGS关键字排序**

**   **对于层次查询如果用order by排序，比如order by last\_name则是先做完层次获得level,然后按last\_name排序，这样破坏了层次，比如特别关注某行的深度，按level排序，也是会破坏层次的。

在oracle10g中，增加了siblings关键字的排序。

语法：**ordersiblings  by <expre>**

**它会保护层次，并且在每个等级中按expre排序。**

**例题：**

select level,ename  
from emp  
start with ename='KING'  
connect by prior empno=mgr;

start with 子句决定了爬树的起点  
connect by prior 主键＝外键 子句决定了爬树的方向:  
主键在前是从上向下爬（反之亦反）

col ename for a30  
select level,lpad(ename,length(ename)+level\*2-2,' ') ename  
from emp  
start with ename='KING'  
connect by prior empno=mgr;

树状结构平铺:  
select sys\_connect\_by\_path(ename,'/') ename  
from emp  
start with ename='KING'  
connect by prior empno=mgr  
and ename!='BLAKE';

剪枝:  
剪枝条件出现在where子句，剪一个节点  
select level,lpad(ename,length(ename)+level\*2-2,' ') ename  
from emp  
where ename!='BLAKE'  
start with ename='KING'  
connect by prior empno=mgr;

剪枝条件出现在connect by prior子句，剪一个分支  
select level,lpad(ename,length(ename)+level\*2-2,' ') ename  
from emp  
start with ename='KING'  
connect by prior empno=mgr  
and ename!='BLAKE';

[https://blog.csdn.net/m0\_37298602/article/details/77472942][https_blog.csdn.net_m0_37298602_article_details_77472942]

[https://blog.csdn.net/ycb1689/article/details/43937089][https_blog.csdn.net_ycb1689_article_details_43937089]

原

# Oracle中使用Start With connect by prior实现树功能 #

2015年02月25日 14:01:38 [ycb1689][] 阅读数：655

1. `select '|' || lpad(level, level * 4 + 1, '_') || '' ||a. member_name member_cname,`
2. `a.member_type,`
3. `decode(a.member_type,'加盟店',decode(a.MEMBER_LEVEL,1,'专门店','2','指导级店','3','资深指导级店','4','地区督导','5','全国督导','6','其它')`
4. `,'咨询师',decode(a.MEMBER_LEVEL,'0','申请中','1','初阶','2','中阶','3','高阶'))member_level,`
5. `a.member_code,`
6. `a.ALONE_BV s_bv,`
7. `a.TUANDUI_BV t_bv,`
8. `a.PROVINCE province_name,`
9. `a.city region_name`
10. `from (select`
11. `member_name,`
12. `MEMBER_TYPE,`
13. `max(MEMBER_LEVEL) MEMBER_LEVEL,`
14. `MEMBER_CODE,`
15. `nvl(sum(ALONE_BV),0) ALONE_BV,`
16. `nvl(sum(TUANDUI_BV),0) TUANDUI_BV,`
17. `PROVINCE,`
18. `CITY,`
19. `RECOMMEND_CODE`
20. `from ALL_MEMBERS_YJ`
21. `where year>=[year_from] and month>=[month_from]`
22. `and year<=[year_to] and month<=[month_to]`
23. `and is_cancel=decode([s_cancle],'4',is_cancel,'1','1',is_cancel)`
24. `group by member_name,MEMBER_TYPE,MEMBER_CODE,PROVINCE,CITY,RECOMMEND_CODE)a`
25. `start with a.member_code =[member_code]`
26. `connect by prior a.member_code = a.recommend_code`

[https://blog.csdn.net/ycb1689/article/details/43937089][https_blog.csdn.net_ycb1689_article_details_43937089]

[/images/20220401/e64f1e293de24f35af3043e80109a543.png][https_blog.csdn.net_wacthamu_article_details_8139713]

转

# oracle 递归 start with connect by level #

2012年11月02日 10:32:39 [wacthamu][] 阅读数：4180

查找员工编号为7369的领导:

1 SELECT LEVEL,E.* FROM EMP E CONNECT BY PRIOR E.MGR = E.EMPNO  START WITH E.EMPNO = 7876
    2 ORDER BY LEVEL DESC

![2011122516183912.jpg][]

"start with" -- this identifies all LEVEL=1 nodes in the tree

"connect by" -- describes how to walk from the parent nodes above to their children and   
their childrens children.

Easiest to use an example on emp. If we start with "where mgr is NULL", we generate the   
set of employees that have no mgr (they are the top of the tree). If we

CONNECT BY PRIOR EMPNO = /\* current \*/ MGR

that will take all of the PRIOR records (the start with at first) and find all records   
such that the MGR column equals their EMPNO (find all the records of people managed by   
the people we started with).

使用WITH语句优化查询结果:优化等级

[![复制代码][copycode.gif]][https_blog.csdn.net_wacthamu_article_details_8139713]

1 WITH A AS
     2  (SELECT MAX(LEVEL) + 1 LVL
     3     FROM EMP E
     4   CONNECT BY PRIOR E.MGR = E.EMPNO
     5    START WITH E.EMPNO = 7876
     6    ORDER BY LEVEL DESC)
     7 SELECT A.LVL 最高等级加1,
     8        LEVEL 当前等级,
     9        A.LVL - LEVEL 优化后等级,
    10        E.* 　FROM A,
    11        EMP E CONNECT BY PRIOR E.MGR = E.EMPNO START WITH E.EMPNO = 7876 ORDER BY LEVEL DESC

[![复制代码][copycode.gif]][https_blog.csdn.net_wacthamu_article_details_8139713]

![2011122516422294.jpg][]

查找员工编号为7839的所有下属(7839为king):

1 SELECT LEVEL 等级, E.*
    2   FROM EMP E
    3 CONNECT BY PRIOR E.EMPNO = E.MGR
    4  START WITH E.EMPNO = 7839

![2011122516473339.jpg][]

\--构造整个的层次结构

1 select lpad(' ',level*2,' ')||ename ename, empno, mgr
    2     from emp
    3     START WITH MGR IS NULL
    4     CONNECT BY PRIOR EMPNO = MGR

![2012011506153052.jpg][]

So, KING is the start with set then JONES BLAKE and CLARK fall under him. Each of them   
becomes the PRIOR record in turn and their trees are expanded.

使用Connect By 结合 level构造虚拟行:

1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

![2011122516515076.jpg][]

使用rownum实现类似的功能:

1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

![2011122516531444.jpg][]

\---------------------待续-----------------------

使用UNION ALL构造两层节点的树:

视图如下所示:

[![复制代码][copycode.gif]][https_blog.csdn.net_wacthamu_article_details_8139713]

1 CREATE OR REPLACE VIEW TREE_VIEW AS
     2 SELECT
     3  '1' AS rootnodeid,
     4  'xxxx有限责任公司' AS treename,
     5  '-1'  AS parent_id
     6 FROM dual
     7 UNION
     8 SELECT
     9   to_char(d.deptno),
    10   d.dname || '_' ||d.loc,
    11   '1' AS parent_id
    12  FROM dept d;

[![复制代码][copycode.gif]][https_blog.csdn.net_wacthamu_article_details_8139713]

查询语句:

1 SELECT T.*, LEVEL
    2   FROM TREE_VIEW T
    3  START WITH T.PARENT_ID = '-1'
    4 CONNECT BY PRIOR T.ROOTNODEID = T.PARENT_ID

![2012030919440697.png][]

\-----以下为更新内容:

1、先查看总共有几个等级：

1 SELECT COUNT(LEVEL)
    2   FROM EMP E
    3 CONNECT BY PRIOR E.EMPNO = E.MGR
    4  START WITH E.MGR IS NULL;

![2012042405445993.png][]

2、查看每个等级的人数。主要是通过LEVEL进行GROUP BY

1 SELECT COUNT(LEVEL)
    2   FROM EMP E
    3 CONNECT BY PRIOR E.EMPNO = E.MGR
    4  START WITH E.MGR IS NULL
    5  GROUP BY LEVEL;

![2012042405460427.png][]

3、Oracle 10g提供了一个简单的connect\_by\_isleaf=1,

0 表示非叶子节点

1 SELECT LEVEL AS 等级, CONNECT_BY_ISLEAF AS 是否是叶子节点, E.*
    2   FROM EMP E
    3 CONNECT BY PRIOR E.EMPNO = E.MGR
    4  START WITH E.MGR IS NULL

![2012042405471670.png][]

4、SYS\_CONNECT\_BY\_PATH

Oracle 9i提供了sys\_connect\_by\_path(column,char),其中column 是字符型或能自动转

换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。

1 SELECT LEVEL AS 等级,
    2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
    3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>')
    4   FROM EMP E
    5 CONNECT BY PRIOR E.EMPNO = E.MGR
    6  START WITH E.MGR IS NULL;

![2012042405501775.jpg][]

5、修剪树枝和节点:

![2012042405524460.png][]

过滤掉编号是7566的数据(修剪节点)，他指的是把这个节点给裁掉，但是并没有破坏树结构，它的子节点还是可以正常的显示。

[![复制代码][copycode.gif]][https_blog.csdn.net_wacthamu_article_details_8139713]

1 SELECT LEVEL AS 等级,
    2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
    3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
    4        E.*
    5   FROM EMP E
    6 WHERE e.empno != 7566
    7 CONNECT BY PRIOR E.EMPNO = E.MGR
    8  START WITH E.MGR IS NULL;

[![复制代码][copycode.gif]][https_blog.csdn.net_wacthamu_article_details_8139713]

![2012042405550464.jpg][]

裁掉编号是7698的节点和它的子节点:

[![复制代码][copycode.gif]][https_blog.csdn.net_wacthamu_article_details_8139713]

1 SELECT LEVEL AS 等级,
    2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
    3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
    4        E.*
    5   FROM EMP E
    6 CONNECT BY PRIOR E.EMPNO = E.MGR
    7        AND E.EMPNO != 7698
    8  START WITH E.MGR IS NULL;

[![复制代码][copycode.gif]][https_blog.csdn.net_wacthamu_article_details_8139713]

![2012042405562342.png][]

6、CONNECT\_BY\_ROOT的使用，oracle10g新增connect\_by\_root,用在列名之前表示此行的根节点的相同列名的值。

[![复制代码][copycode.gif]][https_blog.csdn.net_wacthamu_article_details_8139713]

1 SELECT LEVEL AS 等级,
    2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
    3        CONNECT_BY_ROOT ENAME,
    4        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
    5        E.*
    6   FROM EMP E
    7 CONNECT BY PRIOR E.EMPNO = E.MGR
    8  START WITH E.MGR IS NULL;

[![复制代码][copycode.gif]][https_blog.csdn.net_wacthamu_article_details_8139713]

![2012042406000634.png][]

对于层次查询如果用order by排序，比如order by last\_name则是先做完层次获得level,然后按last\_name 排序，这样破坏了层次，比如特别关注某行的深度，按level 排序，也是会破坏层次的。在oracle10g中，增加了siblings 关键字的排序。

语法：order siblings by <expre>

它会保护层次，并且在每个等级中按expre排序。

[![复制代码][copycode.gif]][https_blog.csdn.net_wacthamu_article_details_8139713]

1 SELECT LEVEL AS 等级,
    2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
    3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
    4        E.*
    5   FROM EMP E
    6 CONNECT BY PRIOR E.EMPNO = E.MGR 
    7  START WITH E.MGR IS NULL
    8  ORDER SIBLINGS BY  E.ENAME;

[![复制代码][copycode.gif]][https_blog.csdn.net_wacthamu_article_details_8139713]

![2012042406014245.png][]

### connect\_by\_iscycle(存在循环，将返回1，否则返回0) ###

The CONNECT\_BY\_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.   
You can specify CONNECT\_BY\_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.

[/images/20220401/e64f1e293de24f35af3043e80109a543.png][https_blog.csdn.net_wacthamu_article_details_8139713]

[https://blog.csdn.net/NIan\_jun/article/details/8826479][https_blog.csdn.net_NIan_jun_article_details_8826479]

原

# Oracle Connect by细说 #

2013年04月19日 23:55:23 [nian\_jun][nian_jun] 阅读数：1982

## **   一、利用Connect by 构造数列** ##

select level from dual connect by level <=10

可用于解决百钱买鸡等列方程式问题：

题：老母鸡2块1只，小母鸡2块3只，大白兔3块1只，小白兔7块4只，要求买回来的动物总共100只，并且脚不少于220条不多于340条。输出所有的可能情况，每种动物都至少要购买一只。

解：

1. `with l as (select rownum n from dual connect by level <50 ),`
2. `x as (select rownum*3 n from dual connect by level <50 ),`
3. `d as (select rownum n from dual connect by level <33 ),`
4. `xb as (select rownum*4 n from dual connect by level <15 )`
5. `select l.n LMJ, x.n XMJ, d.n DBT, xb.n XBT`
6. `from l, x, d, xb`
7. `where l.n * 2 + x.n * 2 / 3 + d.n * 3 + xb.n * 7 / 4 = 100`
8. `and 2 * (l.n + x.n) + 4 * (d.n + xb.n) between 220 and 340`
9. `and l.n + x.n + d.n + xb.n = 100`
10. `and (l.n + x.n) between 30 and 90`
11. `and (d.n + xb.n) between 10 and 70 --多余限制条件为提高效率`

## 二、 START WITH .. CONNECT BY PRIOR.. ##

> oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询，connect by 是结构化查询中用到的，其基本语法是：
> 
> select ... from <TableName> where <Conditional-1> start with <Conditional-2> connect by <Conditional-3> ;
> 
> <Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。
> 
> <Conditional-2>：是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。
> 
>   <Conditional-3>：是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org\_id = parent\_id就是说上一条记录的org\_id 是本条记录的parent\_id，即本记录的父亲是上一条记录。
> 
> 早扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：
> 
> 第一步：从根节点开始；
> 
> 第二步：访问该节点；
> 
> 第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；
> 
> 第四步：若该节点为根节点，则访问完毕，否则执行第五步；
> 
> 第五步：返回到该节点的父节点，并执行第三步骤。
> 
> 总之：扫描整个树结构的过程也即是中序遍历树的过程。
> 
> 1． 树结构的描述
> 
> 树结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如EMP表中的EMPNO和MGR。EMPNO表示该雇员的编号，MGR表示领导该雇员的人的编号，即子节点的MGR值等于父节点的EMPNO值。在表的每一行中都有一个表示父节点的MGR（除根节点外），通过每个节点的父节点，就可以确定整个树结构。
> 
> 在SELECT命令中使用CONNECT BY 和蔼START WITH 子句可以查询表中的树型结构关系。其命令格式如下：
> 
> SELECT 。。。
> 
> CONNECT BY \{PRIOR 列名1=列名2|列名1=PRIOR 裂名2\}
> 
> \[START WITH\]；
> 
> 其中：CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。
> 
> START WITH： 不但可以指定一个根节点，还可以指定多个根节点。
> 
> 2．关于PRIOR
> 
> 运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。
> 
> PRIOR被置于CONNECT BY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：
> 
> CONNECT BY PRIOR EMPNO=MGR
> 
> PIROR运算符被置于CONNECT BY 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：
> 
> CONNECT BY EMPNO=PRIOR MGR
> 
> 在这种方式中也应指定一个开始的节点。
> 
> 3． 定义查找起始节点
> 
> 在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。
> 
> 4．使用LEVEL
> 
> 在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2。
> 
> 5．节点和分支的裁剪
> 
> 在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。
> 
> 6．排序显示
> 
> 象在其它查询中一样，在树结构查询中也可以使用ORDER BY 子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序。
> 
> 例：
> 
>  
> 
>  
> 
> 1. `create table t2(`
> 2. `root_id number,`
> 3. `id number,`
> 4. `name varchar(5),`
> 5. `description varchar(10)`
> 6. `);`
> 7. `insert into t2(root_id,id,name,description) values(0,1,'a','aaa');`
> 8. `insert into t2(root_id,id,name,description) values(1,2,'a1','aaa1');`
> 9. `insert into t2(root_id,id,name,description) values(1,3,'a2','aaa2');`
> 10. `insert into t2(root_id,id,name,description) values(0,4,'b','bbb');`
> 11. `insert into t2(root_id,id,name,description) values(4,5,'b1','bbb1');`
> 12. `insert into t2(root_id,id,name,description) values(4,6,'b2','bbb2');`
> 13. `select * from t2;`
> 14. `select * from t2 start with root_id =0 connect by prior id= root_id;`
> 
>  
> 
> ## 三 深入了解  ##
> 
>  
> 
> ### 1. [ 深入理解connect by][connect by]  (帖子地址) ###
> 
> 对单记录/单条数据使用connect by 正常，多条使用需要注意，其本质为每一条记录都作为自己或者其他记录的叶子进行连接
> 
>  
> 
> select id,level from t connect by level<4; 
> 
>  
> 
> 其自连接的39条结果分布为
> 
> ![1366387528_5745.jpg][]
> 
> 
> 
> 用sql直观展示为（38\#）
> 
>  
> 
> 1. `select rownum,`
> 2. `level,`
> 3. `sys_connect_by_path(id, ',') path,`
> 4. `id,`
> 5. `connect_by_isleaf isleaf`
> 6. `from t`
> 7. `connect by nocycle level < 4`
> 8. `order by rownum, level, path;`
> 
>  
> 
> ### 2. GROUP BY 已知name 和name出现次数反求数据 ###
> 
> 
> 
> a
> 
>  
> 
>  
> 
> 1. `WITH data AS (`
> 2. `SELECT 'A' name, 2 cnt FROM DUAL`
> 3. `UNION ALL SELECT 'B', 4 FROM DUAL`
> 4. `UNION ALL SELECT 'C', 1 FROM DUAL`
> 5. `)`
> 6. `SELECT name`
> 7. `FROM data`
> 8. `CONNECT BY name=PRIOR name AND LEVEL<=cnt ANDPRIOR SYS_GUID() IS NOT NULL;`
> 
>  
> 
> 其中连接条件PRIOR SYS\_GUID() IS NOT NULL 的解释为：
> 
> # CONNECT BY 相当于是一个递归的自连接，不断地把每层的连接结果叠加到结果集中。两层之间的连接条件和递归出口写在CONNECT BY中。 在这的数据并无父子关系，只是要让同一行数据重复出现，因此连接的条件只用到了表的主键id=PRIOR id, 此外再用LEVEL控制层数作为递归出口 。但ORACLE有个检查，如果你有前后连接条件(id=PRIOR id)，但是同一行数据再次出现， 它就会报一个错：ERROR:ORA-01436: CONNECT BY loop in user data 为了欺骗它，这里用了一个PRIOR DBMS\_RANDOM.VALUE, 因为DBMS\_RANDOM.VALUE每次调用都返回不同结果，所以它认为两行数据不一样，所以不报错了。 #
> 
> 递归写法
> 
>  
> 
> 1. `WITH data AS (`
> 2. `SELECT 'A' name, 2 cnt FROM DUAL`
> 3. `UNION ALL SELECT 'B', 4 FROM DUAL`
> 4. `UNION ALL SELECT 'C', 1 FROM DUAL`
> 5. `),`
> 6. `rdata (name, cnt, lv) as`
> 7. `(`
> 8. `select name, cnt, 1 as lv from data`
> 9. `union all`
> 10. `select name, cnt, lv + 1 as lv from rdata where lv + 1 <= cnt`
> 11. `)`
> 12. `select * from rdata`
> 13. `order by name, lv;`
> 
>  
> 
>  
> 
> 以上资料大多为论坛学习资料总结和整理，深入学习connect by
> 
>

[https://blog.csdn.net/NIan\_jun/article/details/8826479][https_blog.csdn.net_NIan_jun_article_details_8826479]

[https://blog.csdn.net/wangshfa/article/details/23780249][https_blog.csdn.net_wangshfa_article_details_23780249]

转

# leve,connect\_by\_isleaf,connect\_by\_iscycle伪列 #

2014年04月15日 19:00:46 [wangshfa][] 阅读数：1968更多

个人分类： [oracle][oracle 4]

level,connect\_by\_isleaf,connect\_by\_iscycle伪列

level 就是这个数据属于 哪一个等级，比如PRESIDENT为1，MANAGER为2

connect\_by\_isleaf 就是树的最末端的值，或者说这个树枝下已经没有树叶了

connect\_by\_iscycle  导致出现死循环的那个树枝

查询scott 的emp 表

SQL> select empno,level,rpad('\*',level,'\*')||ename name,job,mgr from emp start with empno=7839 connect by prior empno=mgr;  
   
EMPNO      LEVEL NAME         JOB         MGR  
\----- ---------- ------------ --------- -----  
 7839          1 \*KING        PRESIDENT                --level 为1  
 7566          2 \*\*JONES      MANAGER    7839          --level 为2  
 7788          3 \*\*\*SCOTT     ANALYST    7566  
 7876          4 \*\*\*\*ADAMS    CLERK      7788  
 7902          3 \*\*\*FORD      ANALYST    7566  
 7369          4 \*\*\*\*SMITH    CLERK      7902  
 7698          2 \*\*BLAKE      MANAGER    7839  
 7499          3 \*\*\*ALLEN     SALESMAN   7698  
 7521          3 \*\*\*WARD      SALESMAN   7698  
 7654          3 \*\*\*MARTIN    SALESMAN   7698  
 7844          3 \*\*\*TURNER    SALESMAN   7698  
 7900          3 \*\*\*JAMES     CLERK      7698

[https://blog.csdn.net/wangshfa/article/details/23780249][https_blog.csdn.net_wangshfa_article_details_23780249]

[https_blog.csdn.net_xuheng8600_article_details_85848660]: https://blog.csdn.net/xuheng8600/article/details/85848660
[https_docs.oracle.com_en_database_oracle_oracle-database_18_sqlrf_Hierarchical-Queries.html_GUID-E3D35EF7-33C3-4D88-81B3-00030C47AE56]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Queries.html#GUID-E3D35EF7-33C3-4D88-81B3-00030C47AE56
[Home]: https://docs.oracle.com/
[Database]: https://docs.oracle.com/en/database/index.html
[Oracle]: https://docs.oracle.com/en/database/oracle/index.html
[Oracle Database]: https://docs.oracle.com/en/database/oracle/oracle-database/index.html
[Release 18]: https://docs.oracle.com/en/database/oracle/oracle-database/18/index.html
[Description of hierarchical_query_clause.eps follows]: /images/20220401/dd538fd1185c419e8e9bd62cd2fd7ac1.png
[Description of the illustration hierarchical_query_clause.eps]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/img_text/hierarchical_query_clause.html
[Conditions]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Conditions.html#GUID-C2E3ED44-16E7-4924-9125-E1693B1022A8
[CONNECT_BY_ISCYCLE Pseudocolumn]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Query-Pseudocolumns.html#GUID-DA181C6B-7B13-41E3-AAF5-5C19963D9D1C
[CONNECT_BY_ROOT]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Query-Operators.html#GUID-875C8985-4AEF-4DF1-BA23-3CDF5BCBCD8E
[2]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Queries.html#GUID-0118DF1D-B9A9-41EB-8556-C6E7D6A5A84E__I2070828
[Figure 9-1]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Queries.html#GUID-0118DF1D-B9A9-41EB-8556-C6E7D6A5A84E__I2066595
[Figure 3-1]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Query-Pseudocolumns.html#GUID-D91FFF59-ECB0-40F0-AB4C-7A9D27EBEEF1__I1009270
[Description of Figure 9-1 follows]: /images/20220401/cfe5ac166b774ac59c6bbf7bb6f352db.png
[Description of _Figure 9-1 Hierarchical Queries]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/img_text/sqlrf002.html
[order_by_clause]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__I2171079
[LEVEL Pseudocolumn]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Query-Pseudocolumns.html#GUID-D91FFF59-ECB0-40F0-AB4C-7A9D27EBEEF1
[SYS_CONNECT_BY_PATH]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/SYS_CONNECT_BY_PATH.html#GUID-D25A0F86-B559-4090-9164-7A2C84D1E11E
[subquery_factoring_clause]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__I2077142
[Previous]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Creating-Simple-Queries.html
[Next]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/The-UNION-ALL-INTERSECT-MINUS-Operators.html
[CONNECT_BY_ISLEAF Pseudocolumn]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Query-Pseudocolumns.html#GUID-7DFBC564-31A6-4C71-A79C-6D3CC0B0BB10
[Hierarchical Queries]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Queries.html#GUID-0118DF1D-B9A9-41EB-8556-C6E7D6A5A84E
[Description of Figure 3-1 follows]: /images/20220401/049e8326ac25463fa7929abf55d22e7a.png
[Description of _Figure 3-1 Hierarchical Tree]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/img_text/sqlrf001.html
[IN Condition]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/IN-Condition.html#GUID-C7961CB3-8F60-47E0-96EB-BDCF5DB1317C
[Previous 1]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Pseudocolumns.html
[Next 1]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Sequence-Pseudocolumns.html
[https_blog.csdn.net_u013766398_article_details_53285327]: https://blog.csdn.net/u013766398/article/details/53285327
[Link 1]: https://me.csdn.net/u013766398
[https_blog.csdn.net_feier7501_article_details_21815691]: https://blog.csdn.net/feier7501/article/details/21815691
[feier7501]: https://me.csdn.net/feier7501
[oracle]: https://blog.csdn.net/feier7501/article/category/1341586
[Center]: /images/20220401/50e2ebb595684c7eb434d7ea0ead2f34.png
[Center 1]: /images/20220401/ce38c4df62e84b15b2397cc2cbc7ea55.png
[Center 2]: /images/20220401/b77d1476e699479982cea0f0e069d29a.png
[https_blog.csdn.net_guaoran_article_details_52918372]: https://blog.csdn.net/guaoran/article/details/52918372
[guaoran]: https://me.csdn.net/guaoran
[Oracle_sql]: http://so.csdn.net/so/search/s.do?q=Oracle%E4%BD%BF%E7%94%A8sql%E6%9F%A5%E8%AF%A2%E5%87%BA%E6%A0%91%E7%8A%B6%E5%9E%8B&t=blog
[Link 2]: http://so.csdn.net/so/search/s.do?q=%E6%A0%B9%E6%8D%AE%E5%AD%90%E8%8A%82%E7%82%B9%E5%92%8C%E7%88%B6%E8%8A%82%E7%82%B9%E6%9F%A5%E8%AF%A2%E5%87%BA%E6%A0%91%E7%8A%B6%E5%9E%8B&t=blog
[start with connect b]: http://so.csdn.net/so/search/s.do?q=start%20with%20connect%20b&t=blog
[oracle 1]: https://blog.csdn.net/guaoran/article/category/6480510
[Center 3]: /images/20220401/8446c8a65a5242b5b5c5a6d5e227f528.png
[Center 4]: /images/20220401/441314607bad449c9b88bb33ada77f33.png
[https_zhidao.baidu.com_question_752780874722290124.html]: https://zhidao.baidu.com/question/752780874722290124.html
[Link 3]: https://www.baidu.com/s?wd=%E6%A0%91%E7%8A%B6%E7%BB%93%E6%9E%84&tn=SE_PcZhidaonwhc_ngpagmjz&rsv_dl=gh_pc_zhidao
[Link 4]: https://www.baidu.com/s?wd=%E4%B8%AD%E5%BA%8F%E9%81%8D%E5%8E%86&tn=SE_PcZhidaonwhc_ngpagmjz&rsv_dl=gh_pc_zhidao
[Link 5]: https://www.baidu.com/s?wd=%E8%87%AA%E9%A1%B6%E5%90%91%E4%B8%8B&tn=SE_PcZhidaonwhc_ngpagmjz&rsv_dl=gh_pc_zhidao
[logo.gif]: https://www.cnblogs.com/Skins/custom/images/logo.gif
[logo.gif 1]: /images/20220401/f67654c1a4154c0888a30eda7e8f1f83.png
[Link 6]: https://www.cnblogs.com/
[Link 7]: https://i.cnblogs.com/EditPosts.aspx?opt=1
[Link 8]: https://msg.cnblogs.com/send/Debugs
[Link 9]: /images/20220401/f67654c1a4154c0888a30eda7e8f1f83.pngrss
[Link 10]: https://i.cnblogs.com/
[oracle_]: /images/20220401/f67654c1a4154c0888a30eda7e8f1f83.pngp/7411037.html
[Oracle 1]: /images/20220401/f67654c1a4154c0888a30eda7e8f1f83.pngcategory/900758.html
[icon_weibo_24.png]: https://common.cnblogs.com/images/icon_weibo_24.png
[wechat.png]: /images/20220401/a4bbb09c38ad42c2973a957fcb7401d0.png
[sample_face.gif]: /images/20220401/2e88b0d221404778b9f687259ffd7d41.png
[sample_face.gif 1]: http://home.cnblogs.com/u/yangpeng-jingjing/
[- 0]: http://home.cnblogs.com/u/yangpeng-jingjing/followees
[- 0 1]: http://home.cnblogs.com/u/yangpeng-jingjing/followers
[Link 11]: /images/20220401/f67654c1a4154c0888a30eda7e8f1f83.pngp/7411010.html
[Link 12]: /images/20220401/f67654c1a4154c0888a30eda7e8f1f83.pngp/7422804.html
[Link 13]: https://i.cnblogs.com/EditPosts.aspx?postid=7411037
[Link 14]: /images/20220401/f67654c1a4154c0888a30eda7e8f1f83.pngp/7411037.html#
[Link 15]: /images/20220401/f67654c1a4154c0888a30eda7e8f1f83.pngp/7411037.html#top
[https_blog.csdn.net_jackpk_article_details_3247549]: https://blog.csdn.net/jackpk/article/details/3247549
[jackpk]: https://me.csdn.net/jackpk
[oracle 2]: http://so.csdn.net/so/search/s.do?q=oracle&t=blog
[manager]: http://so.csdn.net/so/search/s.do?q=manager&t=blog
[path]: http://so.csdn.net/so/search/s.do?q=path&t=blog
[up]: http://so.csdn.net/so/search/s.do?q=up&t=blog
[user]: http://so.csdn.net/so/search/s.do?q=user&t=blog
[Link 16]: http://so.csdn.net/so/search/s.do?q=%E6%95%B0%E6%8D%AE%E5%BA%93&t=blog
[oracle 3]: https://blog.csdn.net/jackpk/article/category/423662
[https_blog.csdn.net_m0_37298602_article_details_77472942]: https://blog.csdn.net/m0_37298602/article/details/77472942
[King_Kwin]: https://me.csdn.net/m0_37298602
[Oracle 2]: http://lib.csdn.net/base/oracle
[https_blog.csdn.net_ycb1689_article_details_43937089]: https://blog.csdn.net/ycb1689/article/details/43937089
[ycb1689]: https://me.csdn.net/ycb1689
[https_blog.csdn.net_wacthamu_article_details_8139713]: /images/20220401/e64f1e293de24f35af3043e80109a543.png
[wacthamu]: https://me.csdn.net/wacthamu
[2011122516183912.jpg]: /images/20220401/0281dc8a2e4b4950b46f33cc2623a7d8.png
[copycode.gif]: http://common.cnblogs.com/images/copycode.gif
[2011122516422294.jpg]: /images/20220401/6f7fea4be72549dd82cdf15be239e2da.png
[2011122516473339.jpg]: /images/20220401/65a584ccaafb45e9ad1821bbbfa740c0.png
[2012011506153052.jpg]: /images/20220401/e596a30583314b069bc35edc9360f554.png
[2011122516515076.jpg]: /images/20220401/5daf4f97b1d64999a6fb7ae220e8590a.png
[2011122516531444.jpg]: /images/20220401/0473a0899fd94d968f3be7e6ee4189a2.png
[2012030919440697.png]: /images/20220401/bfd9628c8a4a4da1b9f86cb3a1115179.png
[2012042405445993.png]: /images/20220401/ffe3ab0e1b4a4443b26202324b33823c.png
[2012042405460427.png]: /images/20220401/97574ba492dd47e3aefbcf9250148ab7.png
[2012042405471670.png]: /images/20220401/51a361d13677461eaafed1ceee9d71f3.png
[2012042405501775.jpg]: /images/20220401/2ef376e091bb436aa53841b642ddab30.png
[2012042405524460.png]: /images/20220401/9b0f693a1b5c4f1484ec16916e7f8dcb.png
[2012042405550464.jpg]: /images/20220401/c5e26c04e5a74bbabfad5a8edf4d3b6e.png
[2012042405562342.png]: /images/20220401/7a7d7f49c8ee4c90bfc8744a717466b5.png
[2012042406000634.png]: /images/20220401/15387d25b43642c397296f9d2ebe81d2.png
[2012042406014245.png]: /images/20220401/103464c58e684045bd4b195bbffecbab.png
[https_blog.csdn.net_NIan_jun_article_details_8826479]: https://blog.csdn.net/NIan_jun/article/details/8826479
[nian_jun]: https://me.csdn.net/NIan_jun
[connect by]: http://www.itpub.net/forum.php?mod=viewthread&tid=994465
[1366387528_5745.jpg]: https://img-my.csdn.net/uploads/201304/20/1366387528_5745.jpg
[https_blog.csdn.net_wangshfa_article_details_23780249]: https://blog.csdn.net/wangshfa/article/details/23780249
[wangshfa]: https://me.csdn.net/wangshfa
[oracle 4]: https://blog.csdn.net/wangshfa/article/category/1258899