CTE 獲取所有後代與父母

架構：

CREATE TABLE item (
   id        int primary key,
   parent_id int,
   FOREIGN KEY(parent_id) REFERENCES item(id)
);

INSERT INTO item
   (id, parent_id)
VALUES
   (1, NULL),
   (2, 1),
   (3, 1),
   (4, 2),
   (5, 3),
   (6, 3),
   (7, 6)
;

詢問：

WITH RECURSIVE descendants(id, parent_id) AS (
   SELECT 1, parent_id FROM item
       UNION ALL
   SELECT item.id, descendants.parent_id
   FROM item, descendants
   WHERE item.parent_id=descendants.id
)
SELECT * FROM descendants;

小提琴：http ://sqlfiddle.com/#!5/27c03/4

目標：給定一些父母（例如2、3）得到它的所有後代。

我想出了以下 CTE，但它返回的項目太多。

SELECT * FROM descendants WHERE parent_id IN (2, 3);

應該給

id | parent_id
4  | 2
5  | 3
6  | 3
7  | 3

這裡的答案很接近，但我的架構有所不同：https ://dba.stackexchange.com/a/94944/253249

用於確定的基礎id用途

WITH RECURSIVE 
descendants(id, parent_id, most_parent_id) AS (
 SELECT item.id, item.parent_id, item.parent_id
 FROM item 
 WHERE item.parent_id = 1      -- given base id value
 UNION ALL
 SELECT item.id, item.parent_id, descendants.most_parent_id
 FROM descendants
 JOIN item ON descendants.id = item.parent_id
)
SELECT id, parent_id 
FROM descendants
WHERE parent_id <> most_parent_id;

對於基id值列表（多個樹或分支）使用

WITH RECURSIVE 
base_ids (id) AS (
 SELECT 1
 UNION ALL
 SELECT 3
),
descendants(id, parent_id, most_parent_id) AS (
 SELECT item.id, item.parent_id, item.parent_id
 FROM base_ids
 JOIN item ON base_ids.id = item.parent_id
 UNION ALL
 SELECT item.id, item.parent_id, descendants.most_parent_id
 FROM descendants
 JOIN item ON descendants.id = item.parent_id
)
SELECT * 
FROM descendants
WHERE parent_id <> most_parent_id;

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=bda692a6854b8af9d53f337e98fd9604

請嘗試讓我知道它是否有幫助：

WITH descendants as
(
 SELECT i.id, i.parent_id, CAST(i.id AS varchar) as Level
 FROM item i
 WHERE i.parent_id is null

 UNION ALL

 SELECT i1.id, i1.parent_id, CAST(i1.id AS varchar) || ', ' || d.Level
 FROM item i1  
 INNER JOIN descendants d ON d.id = i1.parent_id
)
SELECT * 
From descendants
where parent_id in (2,3)

展示

參考

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