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

Question

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

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);

有了这些值……

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 中是如何完成的？

Answer 1

在 Postgres 中使用 RECURSIVE CTE:

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;

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

• Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle?

或者您可以安装附加模块tablefunc，它提供函数connectby()。几乎一样。参见 Stradas' answer了解详情。