解決如下：https ://stackoverflow.com/a/71149082/8082262
我有兩張桌子：community2collection(collection_id,community_id)和community2community(child_comm_id,parent_comm_id)。
我正在嘗試創建一個遞回視圖，對于任何給定的集合（collection_id）將回傳直接社區父級（community_id）以及直接社區的任何祖先的串列（因此遞回地通過community2community.
我有
CREATE VIEW hierarchical_comcoltree
AS
WITH RECURSIVE comcoltree AS
(SELECT collection_id,
community_id AS any_community_id
FROM community2collection
UNION ALL SELECT com2col.collection_id,
com2com.parent_comm_id AS any_community_id
FROM community2collection com2col
INNER JOIN community2community com2com
ON com2com.child_comm_id = com2col.community_id)
SELECT * FROM comcoltree;
但這僅檢索直接社區，以及它的第一個父母，而不是進一步的祖先，查詢
SELECT collection_id,
any_community_id
FROM hierarchical_comcoltree
WHERE collection_id = '13081d45-dcdc-4e4d-94a5-33056c9f0c49'
ORDER BY collection_id ASC;
任何幫助將不勝感激，嘗試了一些變化（以及功能），但還沒有找到一個可行的......
測驗資料
community2collection
| collection_id | community_id |
|---|---|
| 13081d45-dcdc-4e4d-94a5-33056c9f0c49 | 17475c16-a03e-4f5f-8928-bef6a4813978 |
| cbc98392-ba86-4ed3-b477-5a4a7652f3f0 | 88aa4abd-d024-47d2-a1a4-bdb3d744dd24 |
community2community
| child_comm_id | parent_comm_id |
|---|---|
| 17475c16-a03e-4f5f-8928-bef6a4813978 | ae12f497-b7de-4cb6-939f-70968a5e7b0a |
| ae12f497-b7de-4cb6-939f-70968a5e7b0a | 05810e9b-cf09-4c13-a750-297cca5fd84f |
| 88aa4abd-d024-47d2-a1a4-bdb3d744dd24 | 726a8531-4441-4feb-a8fc-b089445bc39e |
| 726a8531-4441-4feb-a8fc-b089445bc39e | ae12f497-b7de-4cb6-939f-70968a5e7b0a |
| ae12f497-b7de-4cb6-939f-70968a5e7b0a | 05810e9b-cf09-4c13-a750-297cca5fd84f |
- 第一次收集的預期結果視圖
'13081d45-dcdc-4e4d-94a5-33056c9f0c49'
= >'17475c16-a03e-4f5f-8928-bef6a4813978'和'ae12f497-b7de-4cb6-939f-70968a5e7b0a''05810e9b-cf09-4c13-a750-297cca5fd84f'
=>當前解決方案缺少最后一個
- 第二次收集的預期結果視圖
'cbc98392-ba86-4ed3-b477-5a4a7652f3f0'
=> '88aa4abd-d024-47d2-a1a4-bdb3d744dd24', '726a8531-4441-4feb-a8fc-b089445bc39e','ae12f497-b7de-4cb6-939f-70968a5e7b0a'和'05810e9b-cf09-4c13-a750-297cca5fd84f'
=>當前解決方案缺少最后兩個
測驗：
WITH RECURSIVE comcoltree AS
(SELECT collection_id,
community_id AS any_community_id
FROM community2collection
UNION ALL SELECT com2col.collection_id,
com2com.parent_comm_id AS any_community_id
FROM community2collection com2col
INNER JOIN community2community com2com ON com2com.child_comm_id = com2col.community_id)
SELECT collection_id,
any_community_id
FROM comcoltree
WHERE any_community_id = 'cbc98392-ba86-4ed3-b477-5a4a7652f3f0'
ORDER BY collection_id ASC;
uj5u.com熱心網友回復：
您的主要問題是您沒有加入遞回 CTE ( comcoltree)。因此，您永遠不會使用UNION. 這是我提供的資訊中最好的：
WITH RECURSIVE comcoltree AS
(
SELECT collection_id
, community_id AS any_community_id
FROM community2collection
WHERE collection_id = 'cbc98392-ba86-4ed3-b477-5a4a7652f3f0'
UNION ALL
SELECT prev.collection_id
, next.parent_comm_id AS any_community_id
FROM comcoltree prev
JOIN community2community next ON prev.any_community_id = next.child_comm_id
)
SELECT DISTINCT
collection_id
, any_community_id
FROM comcoltree
;
這是一個 DBFiddle 來展示它的作業原理。我把 distinct 放在那里，因為結果集中有重復，但我認為這是由于樣本資料中有重復。
ae12f497-b7de-4cb6-939f-70968a5e7b0a 05810e9b-cf09-4c13-a750-297cca5fd84f
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/424987.html
標籤：sql PostgreSQL 看法 递归查询
上一篇：用于查找多列匹配（重復）的SQL