我正在嘗試獲取祖父母資料的最終子資料。
我有以下 2 個表，一個是具有父子資料的主表，另一個是該主表資料的關系表。
基于這些資料，我想要父資料及其最終子資料。
為此，我嘗試了以下遞回查詢，但沒有獲得任何相關資料。
最后，我想通過 java 批處理以下面的方式使用這些資料。
如果我一一傳遞 child_data 331 和 327，那么它將分別提供以下結果。
@set ko_id = '331'
select parent_id,child_id,count(parent_id) from (
WITH RECURSIVE ancestors (parent_id) AS (
SELECT distinct t.parent_id ,t.parent_id as extra_id,t.child_id , msok.data_type -- and find all its ancestors
FROM public.data_relation AS t
JOIN data_relation AS a ON t.child_id = a.parent_id or t.child_id = a.child_id
left join data_master msok on msok.id = t.parent_id
where a.child_id = :ko_id
),
descendants (parent_id) AS (
SELECT parent_id ,extra_id as extra_id,child_id, data_type FROM ancestors
UNION ALL
SELECT t.child_id,d.parent_id as extra_id,t.child_id, msok.data_type -- and find all their descendants
FROM public.data_relation AS t
JOIN descendants AS d ON t.parent_id = d.parent_id
left join data_master msok on msok.id = t.child_id
)
SELECT
parent_id, extra_id, child_id, data_type
FROM
descendants where data_type ='1') abc group by parent_id,child_id
uj5u.com熱心網友回復：
這個查詢應該有幫助。第一個 CTE（“with”部分）提供表中的所有關系。第二個 CTE 過濾器只結束子記錄。
最后一部分 - 主要查詢 - 搜索小時候有 331 的父母并向您展示這些。
with recursive result_q as (
select 1 debug_step, parent_data, child_data
from data_rel
union all
select debug_step 1, q.parent_data, r.child_data
from data_rel r
join result_q q
on q.child_data = r.parent_data
),
parent_end_childs as (
select q.parent_data, q.child_data
from result_q q
where not exists (select 1 from data_rel r where r.parent_data = q.child_data)
order by parent_data, child_data)
select pec.*
from parent_end_childs pec
join parent_end_childs pec_1
on pec.parent_data = pec_1.parent_data
where pec_1.child_data = 331
資料庫小提琴
uj5u.com熱心網友回復：
您可以嘗試創建多級父子關系作為 jsonb 陣列：
WITH RECURSIVE list (array_parent_child_data) AS
(
SELECT to_jsonb(array[t.parent_data, t.child_data])
FROM public.data_relation AS t
UNION ALL
SELECT t.parent_data || l.array_parent_child_data
FROM public.data_relation AS t
INNER JOIN list AS l
ON l.array_parent_child_data->0 = t.child_data
AND NOT l.array_parent_child_data @> to_jsonb(t.parent_data) -- this condition is to avoid loops
)
SELECT l2.array_parent_child_data->>0 :: integer AS parent_data
, l2.array_parent_child_data->>-1 :: integer AS end_child_data
FROM list AS l1
LEFT JOIN list AS l2
ON l1.array_parent_child_data <> l2.array_parent_child_data
AND l1.array_parent_child_data @> l2.array_parent_child_data
WHERE l1 IS NULL -- l2.array_parent_child_data is not included in any l1.array_parent_child_data
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/343268.html
標籤：sql PostgreSQL 春批 递归查询