- 浏览: 788120 次
- 性别:
- 来自: 广州
文章分类
最新评论
Hierarchical Queries
If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause:
hierarchical_query_clause::=
start with condition
connect by prior a=b | connect by a = prior b
[cycle | nocycle]
order [siblings] by
Description of the illustration hierarchical_query_clause.gif
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 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 for more information about this operator and "Hierarchical Query Examples"
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:
Oracle selects the root row(s) of the hierarchy—those rows that satisfy the START WITH condition.
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.
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.
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.
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 2-1, "Hierarchical Tree".
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 .
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 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 below 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
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" 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
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 2-1 shows the nodes of an inverted tree with their LEVEL values.
If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause:
hierarchical_query_clause::=
start with condition
connect by prior a=b | connect by a = prior b
[cycle | nocycle]
order [siblings] by
Description of the illustration hierarchical_query_clause.gif
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 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 for more information about this operator and "Hierarchical Query Examples"
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:
Oracle selects the root row(s) of the hierarchy—those rows that satisfy the START WITH condition.
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.
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.
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.
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 2-1, "Hierarchical Tree".
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 .
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 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 below 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
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" 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
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 2-1 shows the nodes of an inverted tree with their LEVEL values.
发表评论
-
Oracle 10g 的clusterware 32位 下载地址
2013-04-19 23:03 1235Oracle 10g 的clusterware 32位 下载地 ... -
oracle 分析函数 RANK()
2013-04-11 00:05 1104RANK()既是一个聚合函数,也是一个分析函数 其具体的语法 ... -
oracle 分析函数
2013-04-09 23:25 1181分析函数是用于计算一组中多行的聚合值,与聚合函数的区别在于聚合 ... -
批量执行 bulk collect与forall用法
2013-04-08 23:49 1398BULK COLLECT 子句会批量检 ... -
pl/sql集合类型
2013-03-26 10:12 1578--集合类型 /* 单行单列的数据,使用标量变量 单行 ... -
oracle 行链接与行迁移
2013-03-16 01:06 1108表里的一行对于一个数据块太大的情况有二种(一行在一个数据块里放 ... -
oracle Health Monitor
2013-01-20 00:02 1619About Health Monitor Beginning ... -
oracle moving window size与 AWR retention period关系
2013-01-19 15:58 8494转自: http://tomszrp.itpub.net/po ... -
Oracle11.2新特性之INSERT提示IGNORE_ROW_ON_DUPKEY_INDEX
2013-01-12 00:20 2931insert提示IGNORE_ROW_ON_DUPKEY_IN ... -
oracle 11g新特性Flashback data archive
2013-01-09 22:52 30891. 什么是flashback data archive F ... -
RMAN List和report 命令
2012-12-25 00:07 2911LIST 命令 使用RMAN LIST 命令显示有关资料档案库 ... -
oracle ASM中ASM_POWER_LIMIT参数
2012-12-24 23:46 6440ASM_POWER_LIMIT 该初始化参数用于指定ASM例程 ... -
oracle I/O 从属进程
2012-12-24 23:24 1435I/O 从属进程 I/O从 ... -
easy connect 之 ORA-12154: TNS: 无法解析指定的连接标识符
2012-12-19 23:43 5645用easy connect连接出现“tns无法解析指定的连接标 ... -
Flashback Database --闪回数据库
2012-12-19 23:38 1396Flashback 技术是以Undo segment中的内容为 ... -
Oracle 11g新特性:Automatic Diagnostic Repository
2012-12-19 22:35 1398Oracle Database 11g的FDI(Fault D ... -
RMAN配置中通道(CHANNEL)相关参数 PARALLELISM 、FILESPERSET的关系
2012-12-19 22:09 2723RMAN配置中通道(CHANNEL)相 ... -
oracle 空间RESUMABLE
2012-12-14 22:05 3064空间RESUMABLE操作 转 Oracle从9i开始 ... -
oracle 创建视图 with check option
2012-12-13 23:14 1539我们来看下面的例子: create or replace vi ... -
flashback transaction闪回事务查询
2012-11-26 22:00 1508闪回事务查询有别于闪回查询的特点有以下3个: (1) ...
相关推荐
Hierarchical-Display 是 Material Design 的分层显示动画效果插件。浏览器支持:Chrome 4 Firefox 16 Opera 12.1 Safari 4 IE 10 Android Browser 4 不支持 Opera Mini 标签:Hierarchical
【Hierarchical RL】分层强化学习:Hierarchical-DQN算法代码 Hierarchical-DQN (Hierarchical Deep Q-Network) 是一种分层强化学习算法,专门设计用于解决复杂的任务,通过将任务分解为层次化的子任务来学习。它...
【Hierarchical RL】分层演员-评论家(Hierarchical Actor-Critic )算法代码 Hierarchical Actor-Critic (HAC) 算法是一种用于分层强化学习(Hierarchical Reinforcement Learning, HRL)的算法,由Levy等人在2019...
We present a simple but powerful algorithm for optimizing the usage of hardware occlusion...can be easily integrated in existing real-time rendering packages based on common hierarchical data structures.
【Hierarchical RL】隐空间分层强化学习(HRL-LS )算法python代码 隐空间分层强化学习,Hierarchical Reinforcement Learning with Latent Space (HRL-LS) 是一种分层强化学习(Hierarchical Reinforcement ...
【Hierarchical RL】动态分层强化学习(DHRL)算法代码 动态分层强化学习,Dynamic Hierarchical Reinforcement Learning (DHRL) 是一种自适应分层强化学习算法,其目标是根据任务和环境的复杂性动态地构建、修改和...
标题和描述中提到的知识点主要涉及“层次化自组织”(Hierarchical Self-Organizing)概念,具体来说,是在于使用一种或多种自组织映射(Self-Organizing Maps, SOM)来揭示数据的固有层次结构。我们下面将详细介绍该...
Oracle中的层次查询是数据库操作中的一个强大特性,它允许我们处理具有层级结构的数据,比如组织结构、地理位置或产品分类等。这种查询主要依赖于`START WITH`和`CONNECT BY`子句,配合`PRIOR`关键字来定义层级关系...
【Hierarchical RL】离线策略修正分层强化学习(HIRO)算法代码 离线策略修正分层强化学习,Hierarchical Reinforcement Learning with Off-Policy Correction (HIRO) 是一种基于分层强化学习的算法,旨在解决长...
分层遗传算法HierarchicalGeneticAlgorithms的文献-2004 Hierarchical Genetic Algorithms.pdf 看到一篇分层遗传算法的文献,和大家分享一下!
分层强化学习(Hierarchical Reinforcement Learning, HRL)是强化学习领域的一个重要分支,其核心思想是通过将复杂的决策过程分解为若干层次的子任务来提高学习效率。本文讨论的论文标题为《Hierarchical ...
### 层次狄利克雷过程 (Hierarchical Dirichlet Process, HDP) #### 概述 层次狄利克雷过程(Hierarchical Dirichlet Process, HDP)是一种非参数贝叶斯模型,用于处理涉及多组数据的聚类问题。在HDP中,每组数据...
层次聚类(Hierarchical Clustering)是数据挖掘和统计分析中的一个重要方法,它通过构建一个树状结构(也称为 dendrogram)来展示数据点之间的相似性或距离关系。在这个树形结构中,每个叶节点代表一个原始数据点,...
分层在线凸优化(Hierarchical Online Convex Optimization,HiOCO)是一种针对异构网络中具有通信延迟的在线凸优化问题的新型算法。在这样的环境中,多个工作者与一个主节点协同执行一系列决策来最小化随时间变化的...
Hierarchical Dirichlet Process简单介绍
研究者提出了一种基于视图的深度学习模型,称为分层视图预测器(Hierarchical View Predictor,HVP),它能够在无监督的情况下,从无序的视图中学习3D形状特征。 在HVP模型中,为了从无序视图中挖掘出高度区分性的...
HierMUD: Hierarchical Multi-task Unsupervised Domain Adaptation between Bridges for Drive-by Damage Diagnosis 通过驾驶车辆的振动响应监测桥梁,通过允许每辆车检查多座桥梁,并消除在每座桥梁上安装和维护...
Chloroplast thylakoid membranes accommodate densely packed protein complexes in ordered, often semi- crystalline arrays and are assembled into highly organized multilamellar systems, an organization ...