如何从表中选择子级的所有父级

使用 recursive cte

WITH Parent AS
(
    SELECT *
        FROM Employees WHERE EmpID IN (8,9)
    UNION ALL
    SELECT emp.*
        FROM Employees emp JOIN Parent ON emp.EmpID = Parent.ManagerID
)
SELECT distinct * FROM Parent

解决方案是找到每个员工的最高层次结构,并使用字符串聚合对他们进行分组。我们可以按照层次结构中的级别顺序获得每个最高管理者下的所有员工姓名。

WITH Parent AS
(
    SELECT *,empid as top_hierarchy,0 as level
        FROM Employees  WHERE EmpID IN (8,9)
    UNION ALL
    SELECT emp.*, parent.top_hierarchy, level+1
        FROM Employees emp JOIN Parent ON emp.EmpID = Parent.ManagerID
)
SELECT STRING_AGG(EmpName,', ') within group(order by level) 
FROM Parent group by top_hierarchy;

http://sqlfiddle.com/#!18/8e17b2/1/0