在postgresql中获取一个孩子的所有家长

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不存在

如何在postgresql中为一个孩子获取所有父母

更多详情在MSQ服务器这里

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