SQL 方言参考/选择查询/层次查询

层次查询

层次查询是一种从与自身关联的表中提取信息的方法。

假设我们有以下表格

My example table: 

id     of type numeric 
father of type numeric, that references an id of other register of the same table 
data   rest of fields, etc

如果我们有以下值

id     father        data 
50     null          The boss
51     50            The well positioned manager
52     50            Another well positioned manager 
53     51            The worker
54     52            Another worker
5      null          Other node 
10     5             The son of Other node

从节点 50 “悬挂” 的值是 50、51、52、53、54，但不是 5 也不 是 10。

• DB2

或者

• Firebird / InterBase

• Ingres、MySQL、MSSQL

• PostgreSQL
• SQLite

WITH RECURSIVE t AS (
    SELECT id, father FROM "table" WHERE id = 50 AND father IS NULL
UNION ALL
    SELECT t1.id, t1.father FROM t JOIN "table" t1 ON (t1.father = t.id)
)
SELECT * FROM t;

• Oracle、Linter

SELECT * FROM table CONNECT BY id = PRIOR father START WITH id = 50