更新时间:2023-02-05 20:18:17
查询-整个树结构:
SELECT *
FROM Employee
START WITH ParentID IS NULL
CONNECT BY PRIOR ID = ParentID
ORDER SIBLINGS BY LastName, FirstName, ID;
查询-给定员工的孩子:
您不需要为此进行分层查询.
(父级由绑定变量:parent_id
给出)
You do not need a hierarchical query for this.
(The parent is given by the bind variable :parent_id
)
SELECT *
FROM Employee
WHERE ParentID = :parent_id
ORDER BY LastName, FirstName, ID;
查询-给定员工的后代:
与整棵树相同的查询,但是起点不同
(父级由绑定变量:parent_id
给出)
The same query as for the whole tree but with a different start point
(The parent is given by the bind variable :parent_id
)
SELECT *
FROM Employee
START WITH ParentID = :parent_id
CONNECT BY PRIOR ID = ParentID
ORDER SIBLINGS BY LastName, FirstName, ID;
查询-员工及其祖先:
与上一个查询类似,但将CONNECT BY
取反,您无需订购兄弟姐妹,因为每个员工只有一名直属经理.
(雇员由绑定变量:employee_id
给出)
Similar to the previous query but with the CONNECT BY
reversed and you won't need to order the siblings as there will only be one immediate manager per employee.
(The employee is given by the bind variable :employee_id
)
SELECT *
FROM Employee
START WITH ID = :employee_id
CONNECT BY PRIOR ParentID = ID;
查询-员工经理:
与上一个查询相同,但具有过滤器LEVEL = 2
以仅获取直接父行.
(雇员由绑定变量:employee_id
给出)
Identical to the previous query but with a filter LEVEL = 2
to just get the immediate parent row.
(The employee is given by the bind variable :employee_id
)
SELECT e.*
FROM Employee e
WHERE LEVEL = 2
START WITH ID = :employee_id
CONNECT BY PRIOR ParentID = ID;