除了 Oracle 之外的 RDBMS 中的 CONNECT BY 或分层查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6401222/
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
CONNECT BY or hierarchical queries in RDBMS other than Oracle
提问by Lukas Eder
Oracle ships with a very handy feature. You can create hierarchical queries (recursive behaviour) using the following clause:
Oracle 附带了一个非常方便的特性。您可以使用以下子句创建分层查询（递归行为）：
CONNECT BY [NOCYCLE] {condition [AND condition...]} [START WITH condition]
As documented here:
如此处所述：
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries003.htm
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries003.htm
I'm wondering, are there any other established RDBMS that support an equivalent or similar syntax? Or can recursive behaviour like this be generically simulated using regular SQL?
我想知道，是否有任何其他已建立的 RDBMS 支持等效或类似的语法？或者可以使用常规 SQL 来模拟这样的递归行为吗？
A good example that I'd like to be able to simulate is this (taken from the Oracle documentation):
我希望能够模拟的一个很好的例子是（取自 Oracle 文档）：
SELECT LPAD(' ', 2 * (LEVEL-1)) || last_name org_chart,
employee_id, manager_id, job_id
FROM employees
START WITH job_id = 'AD_VP'
CONNECT BY PRIOR employee_id = manager_id;
Resulting in:
导致：
ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID
------------------ ----------- ---------- ----------
Kochhar 101 100 AD_VP
Greenberg 108 101 FI_MGR
Faviet 109 108 FI_ACCOUNT
Chen 110 108 FI_ACCOUNT
Sciarra 111 108 FI_ACCOUNT
Urman 112 108 FI_ACCOUNT
Popp 113 108 FI_ACCOUNT
Whalen 200 101 AD_ASST
Mavris 203 101 HR_REP
Baer 204 101 PR_REP
Higgins 205 101 AC_MGR
Gietz 206 205 AC_ACCOUNT
De Haan 102 100 AD_VP
Hunold 103 102 IT_PROG
Ernst 104 103 IT_PROG
Austin 105 103 IT_PROG
Pataballa 106 103 IT_PROG
Lorentz 107 103 IT_PROG
The LEVELpseudo column and the indentation achieved with it is not so important to me
该LEVEL用它来实现伪列和压痕对我来说并不那么重要
采纳答案by De los Tilos
There is an article on the developerworks site Port CONNECT BY to DB2that does a nice conversion. Also an interesting article on Explain Extended (Quassnoi's blog) that shows some difference between CONNECT BY and recursive CTE: Adjacency list vs. nested sets: Oracle, being row-based and set-based. He has also a nice article about "SQL Server: are the recursive CTE's really set-based?". It seems that the "recursive CTE in Oracle is also not set based". I hope this helps with the conversion, recursion in JOOQ and understanding the difference of both implementations of recursion in SQL.
developerworks 站点上有一篇文章Port CONNECT BY to DB2进行了很好的转换。还有一篇关于 Explain Extended（Quassnoi 的博客）的有趣文章，它显示了 CONNECT BY 和递归 CTE 之间的一些区别：邻接列表与嵌套集：Oracle，基于行和基于集合。他还有一篇关于“SQL Server：递归 CTE 真的是基于集合的吗？”的好文章。似乎“Oracle 中的递归 CTE 也不是基于设置的”。我希望这有助于 JOOQ 中的转换、递归以及理解 SQL 中两种递归实现的区别。
Regards, JJ.
问候，JJ。
回答by Codo
SQL Server uses common table expressions(WITH statement) to achieve the same (see Recursive Queries Using Common Table Expressions).
SQL Server 使用公用表表达式（WITH 语句）来实现相同的功能（请参阅使用公用表表达式的递归查询）。
This kind of query can also be used in Oracle (starting with 11g if I'm not mistaken).
这种查询也可以在 Oracle 中使用（如果我没记错的话，从 11g 开始）。
The resulting query is more complex:
结果查询更复杂：
WITH emp(employee_id, manager_id, job_id, last_name, lvl)
AS (
SELECT e.employee_id, e.manager_id, e.job_id, e.last_name, 1 lvl
FROM employees e
WHERE job_id = 'AD_VP'
UNION ALL
SELECT e.employee_id, e.manager_id, e.job_id, e.last_name, r.lvl + 1 lvl
FROM employees e
JOIN emp r ON r.employee_id = e.manager_id
)
SELECT LPAD(' ', 2 * (lvl-1)) || last_name org_chart,
employee_id, manager_id, job_id
FROM emp;
回答by Chris Walton
A trawl through SO showed the following questions and answers that deal with hierarchical queries over a variety of databases. The last of these refers to a MySql resource, that gives a generic SQL approach.
通过 SO 进行的拖网展示了以下问题和答案，这些问题和答案涉及对各种数据库的分层查询。最后一个是指MySql 资源，它提供了一个通用的 SQL 方法。
Building a Table Dependency Graph With A Recursive Query
Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)
