与Oracle的CONNECT BY ... START WITH等效的PostgreSQL语法是什么? [英] What is the equivalent PostgreSQL syntax to Oracle's CONNECT BY ... START WITH?
本文介绍了与Oracle的CONNECT BY ... START WITH等效的PostgreSQL语法是什么?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在 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);
具有这些值...
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 中如何完成?
推荐答案
在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:
这篇关于与Oracle的CONNECT BY ... START WITH等效的PostgreSQL语法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文