SQL 与 Oracle 的 CONNECT BY ... START WITH 等效的 PostgreSQL 语法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24898681/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the equivalent PostgreSQL syntax to Oracle's CONNECT BY ... START WITH?
提问by dacracot
In Oracle, if I have a table defined as …
在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);
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
This query syntax …
这个查询语法……
SELECT
value
FROM
taxonomy
CONNECT BY
PRIOR key = taxHier
START WITH
key = 0;
Will yield …
会产生……
zero
one
four
two
five
six
three
How is this done in PostgreSQL?
这是如何在PostgreSQL 中完成的？
回答by Erwin Brandstetter
Use a RECURSIVE CTEin Postgres:
RECURSIVE CTE在 Postgres 中使用 a ：
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:
我之前的回答中的详细信息和文档链接：
回答by Stradas
Postgres does have an equivalent to the connect by. You will need to enable the module. Its turned off by default.
Postgres 确实有一个相当于 connect by 的东西。您将需要启用该模块。它默认关闭。
It is called tablefunc. It supports some cool crosstab functionality as well as the familiar "connect by" and "Start With". I have found it works much more eloquently and logically than the recursive CTE. If you can't get this turned on by your DBA, you should go for the way Erwin is doing it.
It is robust enough to do the "bill of materials" type query as well.
它被称为tablefunc。它支持一些很酷的交叉表功能以及熟悉的“ connect by”和“ Start With”。我发现它比递归 CTE 更有说服力和逻辑性。如果您的 DBA 无法启用此功能，您应该采用 Erwin 的做法。
它也足够强大，可以执行“物料清单”类型的查询。
Tablefunc can be turned on by running this command:
可以通过运行以下命令来打开 Tablefunc：
CREATE EXTENSION tablefunc;
Here is the list of connection fields freshly lifted from the official documentation.
这是从官方文档中新提取的连接字段列表。
Parameter: Description
relname: Name of the source relation (table)
keyid_fld: Name of the key field
parent_keyid_fld: Name of the parent-key field
orderby_fld: Name of the field to order siblings by (optional)
start_with: Key value of the row to start at
max_depth: Maximum depth to descend to, or zero for unlimited depth
branch_delim: String to separate keys with in branch output (optional)
You really should take a look at the docs page. It is well written and it will give you the options you are used to. (On the doc page scroll down, its near the bottom.)
你真的应该看看文档页面。它写得很好，它将为您提供习惯的选项。（在文档页面向下滚动，靠近底部。）
Postgreql "Connect by" extensionBelow is the description of what putting that structure together should be like. There is a ton of potential so I won't do it justice, but here is a snip of it to give you an idea.
Postgreql "Connect by" 扩展下面是对将该结构组合在一起应该是什么样子的描述。有很多潜力，所以我不会公正地对待它，但这里有一个片段给你一个想法。
connectby(text relname, text keyid_fld, text parent_keyid_fld
[, text orderby_fld ], text start_with, int max_depth
[, text branch_delim ])
A real query will look like this. Connectby_tree is the name of the table. The line that starting with "AS" is how you name the columns. It does look a little upside down.
真正的查询将如下所示。Connectby_tree 是表的名称。以“AS”开头的行是您命名列的方式。它看起来确实有点颠倒。
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text, pos int);
回答by sunrelax
As indicated by Stradas I report the query:
正如 Stradas 所指出的，我报告了以下查询：
SELECT value
FROM connectby('taxonomy', 'key', 'taxHier', '0', 0, '~')
AS t(keyid numeric, parent_keyid numeric, level int, branch text)
inner join taxonomy t on t.key = keyid;
