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