如何将oracle分层查询转换为postgresql? [英] How to convert oracle hierarchical queries to postgresql?

问题描述

我想将下面提到的oracle层次查询转换为postgresql

I want to convert below mentioned oracle hierarchical query to postgresql

SELECT catalog_id, sub_tree_id
FROM   my_catalog
CONNECT BY PRIOR catalog_id = sub_tree_id;

我尝试使用以下postgresql查询，但未获得预期结果

I have tried using the following postgresql query but not getting the expected result

WITH RECURSIVE q AS (
SELECT po.catalog_id,po.sub_tree_id
  FROM my_catalog po
UNION ALL
SELECT po.catalog_id,po.sub_tree_id
  FROM my_catalog po
  JOIN q ON q.catalog_id=po.sub_tree_id
)
SELECT * FROM q;

ORACLE OUTPUT(预期结果)

ORACLE OUTPUT(EXPECTED RESULT)

POSTGRESQL输出(实际结果)

POSTGRESQL OUTPUT(ACTUAL RESULT)

推荐答案

在PostgreSQL中，递归查询是通过首先指定行的 initial (非递归项)来构造的>，即位于层次结构的根或最后一级的那些).随后的迭代(在递归项中，在UNION ALL之后的子查询)然后从输入行集中的其余行中将行添加到结果集中 ，直到没有添加了更多行.

In PostgreSQL recursive queries are constructed by first specifying the initial set of rows (the non-recursive term, i.e. those at the root or final level of the hierarchy). Subsequent iterations (over the recursive term, the sub-query after the UNION ALL) then add rows to the result set from the remaining rows in the input row set until no more rows are added.

在您的情况下，初始子查询未过滤，因此您只需在初始运行中添加所有行，而为后续运行保留任何内容.

In your case, the initial sub-query is unfiltered so you simply add all rows on the initial run, leaving nothing for subsequent runs.

请尝试以下操作:

WITH RECURSIVE q AS (
  SELECT po.catalog_id,po.sub_tree_id
  FROM my_catalog po
  WHERE sub_tree_id = 0  -- this initially selects only "root" rows
UNION ALL
  SELECT po.catalog_id,po.sub_tree_id
  FROM my_catalog po
  JOIN q ON q.catalog_id=po.sub_tree_id
)
SELECT * FROM q;

这篇关于如何将oracle分层查询转换为postgresql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！