Sqlite
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)