connect by prior mgr=empno表示empno的上级是mgrlevel是伪列,表示深度
prior代表等于上一行的字段 prior mgr=empno empno等于上一行的mgr START WITH specifies the root row(s) of the hierarchy. CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. 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. For example:CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id In addition, the CONNECT BY condition cannot contain a subquery.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.
prior mgr=empno
empno等于上一行的mgr
START WITH specifies the root row(s) of the hierarchy.
CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. 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. For example:CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id
In addition, the CONNECT BY condition cannot contain a subquery.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.