我想在SQL中显示一个包含子节点和父节点的树结构。我有一张这样的桌子:
Employee
-------------
ID (int)
FirstName (varchar)
LastName (varchar)
ParentID (int)
Job (varchar)
其代表雇员ParentID代表员工的经理。我想要这张只有这种结构的桌子。
- 我想展示一下整棵树的结构
- 我只想显示子节点
- 我只想显示父节点
SampleDataImage
查询-整个树结构:
SELECT *
FROM Employee
START WITH ParentID IS NULL
CONNECT BY PRIOR ID = ParentID
ORDER SIBLINGS BY LastName, FirstName, ID;
查询-给定员工的子女:
您不需要对此进行分层查询
(父级由绑定变量:parent_id
给定)
SELECT *
FROM Employee
WHERE ParentID = :parent_id
ORDER BY LastName, FirstName, ID;
查询-给定员工的后代:
与整个树的查询相同,但起点不同
(父级由绑定变量: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
给定)
SELECT *
FROM Employee
START WITH ID = :employee_id
CONNECT BY PRIOR ParentID = ID;
查询-员工的经理:
与上一个查询相同,但使用过滤器LEVEL = 2
仅获取直接父行
(员工由绑定变量:employee_id
给定)
SELECT e.*
FROM Employee e
WHERE LEVEL = 2
START WITH ID = :employee_id
CONNECT BY PRIOR ParentID = ID;