我正在為論壇開發一個資料庫，其中包含執行緒和訊息。執行緒以帶有 no 的訊息開頭parent_id；回復是帶有parent_id.
我有一張資訊表。每個專案參考同一張表上的專案，將它們作為父子關聯。
create table messages(
id int,
title text,
content text,
parent_id int
);
現在我用一些資料填充表格：
insert into messages values
(1, 'One', 'First thread main post', null), -- First thread
(2, 'Two', 'First thread reply', 1),
(3, 'Three', 'First thread reply', 2),
(4, 'Four', 'Second thread main post', null), -- Second thread
(5, 'Five', 'Second thread reply', 4),
(6, 'Six', 'Second thread reply', 5),
(7, 'Six', 'Second thread reply', 6),
(8, 'Six', 'Second thread reply', 7),
(9, 'Six', 'Second thread reply', 8);
知道id執行緒的第一條訊息，我可以使用遞回查詢檢索所有回復：
with recursive my_tree as (
select * from messages
where parent_id IS null and id = 4
union all
select messages.* from messages
join my_tree on messages.parent_id = my_tree.id
) select my_tree.*
from my_tree;
這是一個小提琴。
現在，假設我知道id一些回復，并且我想獲取id執行緒的第一條訊息。我該怎么做？
編輯：我知道我可以從已知的id向上檢索執行緒的所有訊息：
with recursive my_tree as (
select * from messages
where id = 9
union all
select messages.* from messages
join my_tree on messages.id = my_tree.parent_id
) select my_tree.*
from my_tree;
這里有一個小提琴。但是，如果執行緒由數千條訊息組成，那么效率就會降低。遞回查詢有沒有辦法在不檢索所有訊息的情況下到達第一項？
uj5u.com熱心網友回復：
對于當前的資料結構，除了訪問從葉子到根的整個路徑之外別無他法。您可以通過僅對關鍵列進行操作來加快搜索速度：
with recursive my_tree as (
select id, parent_id
from messages
where id = 9
union all
select m.id, m.parent_id
from messages m
join my_tree t on m.id = t.parent_id
)
select m.*
from messages m
join my_tree t using(id)
where t.parent_id is null
db<>小提琴。
如果這個方案不滿意，可以考慮給表加一列
alter table messages add root_id int;
用遞回查詢填充此列將很容易，您將可以立即訪問每個節點的根。
另一種方法是使用ltree 擴展更改資料結構。
uj5u.com熱心網友回復：
您的查詢已經在檢索最少的行數。找到原始父級的唯一方法是檢索一個父級，然后檢索那個父級，......并堅持下去，直到找到最終的父級。
關于遞回查詢的遞回部分，您可能不知道的一件事是，每個步驟僅適用于前一步的結果，而不適用于迄今為止累積的 UNION 結果。這意味著通過您的查詢示例的每個回圈僅檢索一行，即其直接父行。很難看出如何提高效率。六個查詢，每個查詢回傳一行。
現在，聽起來您對保留中間行不感興趣，您只想要最終父級的 ID。如果您使用第二個 CTE 對遞回查詢結果集進行后處理，則可以只回傳該一條記錄。我像這樣添加到您的原始查詢中：
with recursive my_tree as (
select *,1 as depth from messages
where parent_id IS null and id = 4
union all
select messages.*, my_tree.depth 1
from messages
join my_tree on messages.parent_id = my_tree.id
), my_root as (select * from my_tree
where depth=(select max(depth)
from my_tree)
)
select *
from my_root;
此添加僅回傳遞回查詢的最后一行。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/476297.html
標籤：sql PostgreSQL 递归查询
上一篇：使用Presto決議JSON列