sql - 在PostgreSQL中为 child 获得所有 parent

我在SQL Server上运行成功。我的系统运行在SQL Server和postgresql上。
但是当我从SQL Server更改为postgresql时，它运行失败。
我的sql如下：

with name_tree as
(
    SELECT DepartNo, ParentNo FROM Departments
    WHERE DepartNo IN (
        SELECT DepartNo FROM BelongToDepartment
        WHERE UserNo = 1)
   union all
   select C.DepartNo, C.ParentNo
   from Departments c
   join name_tree p on C.DepartNo = P.ParentNo
    AND C.DepartNo<>C.ParentNo
)
select * from name_tree

错误是：
命名树不存在
如何在postgresql中为一个孩子找到所有的父母
MSQ Serverhere

最佳答案

尝试：

with RECURSIVE name_tree as
(
    SELECT DepartNo, ParentNo FROM Departments
    WHERE DepartNo IN (
        SELECT DepartNo FROM BelongToDepartment
        WHERE UserNo = 1)
   union all
   select C.DepartNo, C.ParentNo
   from Departments c
   join name_tree p on C.DepartNo = P.ParentNo
    AND C.DepartNo<>C.ParentNo
)
select * from name_tree