如何在 SQL 中查詢與節點的所有子節點（遞歸）關聯的所有實體？

這個問題一般是關於 SQL 的。專門為 MySQL 回答會有所幫助，但不是必需的。

---

好吧，我很難用語言表達……所以請耐心等待。

假設我有一棵事物樹（我將它們稱為節點），並帶有一個看起來像這樣的表（結構可以更改；這只是一個簡單的版本）：

+---------+-----------+
| node_id | parent_id |
+---------+-----------+
| 1       | NULL      |
| 2       | NULL      |
| 3       | 1         |
| 4       | 1         |
| 5       | 5         |
| 7       | 2         |
| 8       | 5         |
+---------+-----------+

然後我有一個實體表。每個實體都與某個節點相關聯。例如（同樣，結構可以改變）：

+-----------+---------+
| entity_id | node_id |
+-----------+---------+
| 1         | 1       |
| 2         | 1       |
| 3         | 4       |
| 4         | 7       |
| ...many more rows   |
+-----------+---------+

這種結構可以表示很多東西，例如每個實體是一部電影，而每個節點是一個流派（但可以有無限級別的子流派）。

因此檢索給定節點的所有實體很簡單；只需對指定的實體表執行查詢node_id。

這是我的問題：我將如何查詢與給定節點關聯的所有實體的實體表，以及它的所有子節點（每個級別，遞歸）。在電影範例中，我想查找特定類型及其所有子類型及其子類型等的所有電影。

我提到了遞歸這個詞，但這並不意味著查詢應該是遞歸的，但在概念上它是遞歸的。查詢應該盡可能快。表的結構也可能需要更改。

謝謝你的幫助！

我假設行 (5,5) 是一個錯字，所以我使用了：

create table tree (node_id int not null primary key, parent_id int);
insert into tree (node_id, parent_id) 
values (1,null),(2,(null),(3,1),(4,1),(5,4),(7,2),(8,5);

作為旁注，如果您要開始回答問題、問題中的 ascii 藝術或創建和插入上述語句，您會更喜歡哪種表示？

要遞歸遍歷樹，您可以使用遞歸公用表表達式 (CTE)：

with rec (node_id, ancestor_id) as (
   select t.node_id, t.parent_id 
   from tree t 
   union all 
   select rec.node_id, t.parent_id 
   from rec, tree t 
   where rec.ancestor_id = t.node_id
) select * from rec

我更喜歡顯式連接，但我使用的 DBMS 在 CTE 中不支持它們：

with rec (node_id, ancestor_id) as (
   select t.node_id, t.parent_id 
   from tree t 
   union all 
   select rec.node_id, t.parent_id 
   from rec
   join tree t 
       on rec.ancestor_id = t.node_id
) select * from rec

現在只需將此結果與您的實體表連接起來：

with rec (node_id, ancestor_id) as (
   select t.node_id, t.parent_id 
   from tree t 
   union all 
   select rec.node_id, t.parent_id 
   from rec
   join tree t 
       on rec.ancestor_id = t.node_id
) select rec.ancestor_id, e,entity_id
 from rec
 join entities e
     on e.node_id = rec.node_id

這假設您的 DBMS 版本支持遞歸 CTE:s。最新版本的 MySQL / MariaDB 可以。

另一個旁注，您可能想要規範化樹關係，並將結構保持在單獨的關係中：

create table treenode (node_id int not null primary key, ...)
create table tree (node_id int not null primary key
                 ,parent_id int not null);

根節點是樹中不存在的節點

如果您使用的是舊版本，或者有大量數據並且您的性能需要改進，您可以向模型添加其他資訊。最常見的是：

• 嵌套集，所有節點都有一個區間，它們包含哪些節點
• 物化路徑，所有節點都有一個代表祖先節點的聚合字元串
• 一個傳遞閉包表，我在tree有一些註釋和一些範例：

引用自：https://dba.stackexchange.com/questions/231530