sql - 在postgresql中获取一个 child 的所有 parent

Question

我在 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

错误是:

name_tree doesn't exist

如何在postgresql中获取一个 child 的所有 parent

MSQ 服务器中的更多详细信息 here

Answer 1

尝试:

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