除Oracle以外的RDBMS中的CONNECT BY或分层查询 [英] CONNECT BY or hierarchical queries in RDBMS other than Oracle

问题描述

Oracle附带了非常方便的功能.您可以使用以下子句创建分层查询(递归行为):

Oracle ships with a very handy feature. You can create hierarchical queries (recursive behaviour) using the following clause:

CONNECT BY [NOCYCLE] {condition [AND condition...]} [START WITH condition]

如此处所述:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries003.htm

我想知道，是否还存在其他支持等效或相似语法的RDBMS?还是可以使用常规SQL来模拟这种递归行为?

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?

我想能够模拟的一个很好的例子是这个(摘自Oracle文档):

A good example that I'd like to be able to simulate is this (taken from the Oracle documentation):

    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; 

结果:

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

LEVEL伪列及其缩进对我而言并不重要

The LEVEL pseudo column and the indentation achieved with it is not so important to me

推荐答案

developerworks网站上有一篇文章

There is an article on the developerworks site Port CONNECT BY to DB2 that 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.

关于， JJ.

这篇关于除Oracle以外的RDBMS中的CONNECT BY或分层查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！