sql - Postgres - 多级父子关系数据
问题描述
我们有一个父子关系,如下所示。
生成表格的脚本如下。
create table dependency ( packageid int, dependant_packageid int);
insert into dependency values (2,1);
insert into dependency values (3,1);
insert into dependency values (4,1);
insert into dependency values (5,2);
insert into dependency values (6,3);
insert into dependency values (7,4);
insert into dependency values (7,5);
insert into dependency values (8,5);
insert into dependency values (8,3);
insert into dependency values (4,5);
insert into dependency values (6,4);
insert into dependency values (5,3);
我们想根据下面提到的查询获取数据。
- 对于给定的包,获取可能的依赖层次结构
Ex:
packageid : 6
Result should be: [(3,1),(4,1),(4,5,2,1),(4,5,3,1)]
packageid : 7
Result should be: [(4,1),(4,5,2,1),(4,5,3,1)]
packageid : 8
Result should be: [(5,2,1),(5,3,1),(3,1)]
- 对于给定的包,获取父包的列表
Ex:
1 - 2,3,4
2 - 5
3 - 6,8,5
4 - 7,6
5 - 7,8,4
- 如果我们需要维护这种父子关系(多对多),理想的模式结构应该是什么(牢记性能)?
感谢任何帮助....快乐的编码....:)
解决方案
这是一个递归查询的快速快照,它为每个 id 吐出不同的完整依赖项列表:
WITH RECURSIVE rcte AS (
--Recursive Seed
SELECT packageid as initialid,
packageid,
dependant_packageid,
CAST(packageid || ',' || dependant_packageid as varchar(30)) as path,
1 as depth
FROM dependency
UNION ALL
--Recursive Term
SELECT initialid,
dependency.packageid,
dependency.dependant_packageid,
CAST(rcte.path || ',' || dependency.dependant_packageid as varchar(30)),
rcte.depth + 1
FROM rcte
INNER JOIN dependency ON rcte.dependant_packageid = dependency.packageid
)
SELECT r1.initialid as packageid, path as dependant_packages
FROM rcte r1
LEFT OUTER JOIN rcte r2
ON r2.path LIKE r1.path || '%' AND r1.depth < r2.depth
WHERE r2.initialid IS NULL
ORDER BY r1.path;
该递归 CTE 有两个部分。recursive seed
运行一次。它从最初将馈送到第二部分的表中收集记录,第二部分recursive term
将迭代直到其 JOIN 失败。在那个递归术语中,我们将 CTE 连接rcte
回表,dependant_packageid
并将 CTE 的 连接到packageid
表的 。
最后,该SELECT
语句引用来自 CTE 和自连接的结果,以从所有这些迭代中找到最长的不同路径。
使用相同的递归逻辑,您可以获得剩余的记录集。
推荐阅读
- multiplayer - 如何在godot中使用代码加入多人游戏?
- c# - 对 Mahapps Metro HamburgerMenuIconItem 的 DataTemplate 应用不同的绑定
- java - Spring 4 MVC 重定向到外部 https 服务器,目标主机被本地主机替换
- java - 分支语句 - 升序或降序整数
- json - Flutter如何在复杂的json中搜索对象
- razor - 级联删除具有关系的实体框架身份用户
- python - Limiting input to 6 integers
- python - UpdateView 不保存更改
- ios - 使用 Flutter、8.0 或更高版本定位 iOS?
- python - 如何导入自定义 python 包?