postgresql – 使用Query递归查找父
栏目: 数据库 · PostgreSQL · 发布时间: 6年前
内容简介:代码日志版权声明:翻译自:http://stackoverflow.com/questions/3699395/find-parent-recursively-using-query
我正在使用postgresql.我的桌子如下所示
parent_id child_id ---------------------- 101 102 103 104 104 105 105 106
我想写一个 sql 查询,它将给出输入的最终父级.
假设我以106作为输入,则其输出为103.
(106 --> 105 --> 104 --> 103)
这是一个完整的例子.首先是DDL:
test=> CREATE TABLE node ( test(> id SERIAL, test(> label TEXT NOT NULL, -- name of the node test(> parent_id INT, test(> PRIMARY KEY(id) test(> ); NOTICE: CREATE TABLE will create implicit sequence "node_id_seq" for serial column "node.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node" CREATE TABLE
…和一些数据…
test=> INSERT INTO node (label, parent_id) VALUES ('n1',NULL),('n2',1),('n3',2),('n4',3); INSERT 0 4 test=> INSERT INTO node (label) VALUES ('garbage1'),('garbage2'), ('garbage3'); INSERT 0 3 test=> INSERT INTO node (label,parent_id) VALUES ('garbage4',6); INSERT 0 1 test=> SELECT * FROM node; id | label | parent_id ----+----------+----------- 1 | n1 | 2 | n2 | 1 3 | n3 | 2 4 | n4 | 3 5 | garbage1 | 6 | garbage2 | 7 | garbage3 | 8 | garbage4 | 6 (8 rows)
这对节点中的每个id执行递归查询:
test=> WITH RECURSIVE nodes_cte(id, label, parent_id, depth, path) AS ( SELECT tn.id, tn.label, tn.parent_id, 1::INT AS depth, tn.id::TEXT AS path FROM node AS tn WHERE tn.parent_id IS NULL UNION ALL SELECT c.id, c.label, c.parent_id, p.depth + 1 AS depth, (p.path || '->' || c.id::TEXT) FROM nodes_cte AS p, node AS c WHERE c.parent_id = p.id ) SELECT * FROM nodes_cte AS n ORDER BY n.id ASC; id | label | parent_id | depth | path ----+----------+-----------+-------+------------ 1 | n1 | | 1 | 1 2 | n2 | 1 | 2 | 1->2 3 | n3 | 2 | 3 | 1->2->3 4 | n4 | 3 | 4 | 1->2->3->4 5 | garbage1 | | 1 | 5 6 | garbage2 | | 1 | 6 7 | garbage3 | | 1 | 7 8 | garbage4 | 6 | 2 | 6->8 (8 rows)
这得到所有后代WHERE node.id = 1:
test=> WITH RECURSIVE nodes_cte(id, label, parent_id, depth, path) AS ( SELECT tn.id, tn.label, tn.parent_id, 1::INT AS depth, tn.id::TEXT AS path FROM node AS tn WHERE tn.id = 1 UNION ALL SELECT c.id, c.label, c.parent_id, p.depth + 1 AS depth, (p.path || '->' || c.id::TEXT) FROM nodes_cte AS p, node AS c WHERE c.parent_id = p.id ) SELECT * FROM nodes_cte AS n; id | label | parent_id | depth | path ----+-------+-----------+-------+------------ 1 | n1 | | 1 | 1 2 | n2 | 1 | 2 | 1->2 3 | n3 | 2 | 3 | 1->2->3 4 | n4 | 3 | 4 | 1->2->3->4 (4 rows)
以下将获取节点的ID为4的路径:
test=> WITH RECURSIVE nodes_cte(id, label, parent_id, depth, path) AS ( SELECT tn.id, tn.label, tn.parent_id, 1::INT AS depth, tn.id::TEXT AS path FROM node AS tn WHERE tn.parent_id IS NULL UNION ALL SELECT c.id, c.label, c.parent_id, p.depth + 1 AS depth, (p.path || '->' || c.id::TEXT) FROM nodes_cte AS p, node AS c WHERE c.parent_id = p.id ) SELECT * FROM nodes_cte AS n WHERE n.id = 4; id | label | parent_id | depth | path ----+-------+-----------+-------+------------ 4 | n4 | 3 | 4 | 1->2->3->4 (1 row)
让我们假设你想限制搜索到深度小于3的后代(请注意,深度还没有增加):
test=> WITH RECURSIVE nodes_cte(id, label, parent_id, depth, path) AS ( SELECT tn.id, tn.label, tn.parent_id, 1::INT AS depth, tn.id::TEXT AS path FROM node AS tn WHERE tn.id = 1 UNION ALL SELECT c.id, c.label, c.parent_id, p.depth + 1 AS depth, (p.path || '->' || c.id::TEXT) FROM nodes_cte AS p, node AS c WHERE c.parent_id = p.id AND p.depth < 2 ) SELECT * FROM nodes_cte AS n; id | label | parent_id | depth | path ----+-------+-----------+-------+------ 1 | n1 | | 1 | 1 2 | n2 | 1 | 2 | 1->2 (2 rows)
我建议使用ARRAY数据类型而不是用于演示“路径”的字符串,但箭头更多地说明了父<=>子关系.
代码日志版权声明:
翻译自:http://stackoverflow.com/questions/3699395/find-parent-recursively-using-query
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:- 使用Nmap查找连接到网络的设备
- [译] 使用 KCSAN 查找 race condition
- unix – 如何在-exec中使用管道查找
- Saluki: 使用静态属性检查查找污点风格的漏洞
- 使用jQuery查找具有特定文本(值)的子元素
- Visual Studio 中使用正则表达式来进行查找替换
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
Open Scene Graph3.0三维视景仿真技术开发详解
国防工业出版社 / 2012-7-1 / 46.00元
OpenSceneGraph 3.0三维视景仿真技术开发详解,ISBN:9787118081411,作者:杨化斌 著 杨化斌 编一起来看看 《Open Scene Graph3.0三维视景仿真技术开发详解》 这本书的介绍吧!