sql - 将 Oracle 中的 CONNECT_BY_ISLEAF 替换为 Postgres

Question

如何将此查询转换为等效的 postgresql 分层查询？如何替换 postgresql 中的 CONNECT_BY_ISLEAF 函数？

SELECT emp_id,mgr_id,name,SYS_CONNECT_BY_PATH (name,'/') PATH ,CONNECT_BY_ISLEAF  ISLEAF   
FROM employee
START WITH  (emp_id = 345) 
CONNECT BY   NOCYCLE (PRIOR emp_id = mgr_id)

Answer 1

这与 Oracle 中使用递归子查询分解子句(又名公用表表达式)的等效项。它应该映射(可能在语法上有一些变化)到 PostgreSQL:

WITH cte ( emp_id, mgr_id, name, path, leaf ) AS (
  SELECT emp_id,
         mgr_id,
         name,
         '/' || name,
         CASE WHEN EXISTS( SELECT 1 FROM employee m WHERE m.mgr_id = e.emp_id )
              THEN 0 ELSE 1 END
  FROM   employee e
  WHERE  emp_id = 345
UNION ALL
  SELECT e.emp_id,
         e.mgr_id,
         e.name,
         c.path || '/' || e.name,
         CASE WHEN EXISTS( SELECT 1 FROM employee m WHERE m.mgr_id = e.emp_id )
              THEN 0 ELSE 1 END
  FROM   employee e
         INNER JOIN cte c
         ON( e.mgr_id = c.emp_id )
)
SELECT * FROM cte;

(注意:这不考虑分层查询的 NOCYCLE 子句 - 如果这是必要的，那么您将需要构建一种机制来消除这些连接。)