与 Oracle 的 CONNECT BY ... START WITH 等效的 PostgreSQL 语法是什么?

2021-12-06 00:00:00 postgresql sql oracle recursive-query connect-by

在Oracle中，如果我有一个表定义为……

In Oracle, if I have a table defined as …

CREATE TABLE taxonomy
    (
    key NUMBER(11) NOT NULL CONSTRAINT taxPkey PRIMARY KEY,
    value VARCHAR2(255),
    taxHier NUMBER(11)
    );
ALTER TABLE
    taxonomy
ADD CONSTRAINT
    taxTaxFkey
FOREIGN KEY
    (taxHier)
REFERENCES
    tax(key);

有了这些值......

With these values …

key value   taxHier
0   zero    null
1   one     0
2   two     0
3   three   0
4   four    1
5   five    2
6   six     2

这个查询语法……

SELECT
     value
FROM
    taxonomy
CONNECT BY
    PRIOR key = taxHier
START WITH
    key = 0;

会产生……

zero
one
four
two
five
six
three

这在 PostgreSQL 中是如何完成的?

How is this done in PostgreSQL?

推荐答案

在 Postgres 中使用 RECURSIVE CTE:

Use a RECURSIVE CTE in Postgres:

WITH RECURSIVE cte AS (
   SELECT key, value, 1 AS level
   FROM   taxonomy
   WHERE  key = 0

   UNION  ALL
   SELECT t.key, t.value, c.level + 1
   FROM   cte      c
   JOIN   taxonomy t ON t.taxHier = c.key
   )
SELECT value
FROM   cte
ORDER  BY level;

我之前回答中的详细信息和文档链接:

Details and links to documentation in my previous answer:

  • PostgreSQL 是否有像LEVEL"这样的伪列?在 Oracle 中?

相关文章