大佬教程收集整理的这篇文章主要介绍了sql – 用于传递闭包的递归查询,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
但是,我的递归查询有些问题.我不熟悉语法,所以这个请求可能完全是我的noobish,为此,我提前道歉.如果运行查询,您将看到节点1在路径结果中重复自身.有人可以帮我弄清楚如何调整sql吗?
/* 1 / \ 2 3 / \ / 4 5 6 / 7 / \ 8 9 */ create table account( acct_id INT,parent_id INT REFERENCES account(acct_id),acct_name varchar(100),PRIMary KEY(acct_id) ); insert into account (acct_id,parent_id,acct_name) values (1,1,'account 1'); insert into account (acct_id,acct_name) values (2,'account 2'); insert into account (acct_id,acct_name) VALUES (3,'account 3'); insert into account (acct_id,acct_name) values (4,2,'account 4'); insert into account (acct_id,acct_name) values (5,'account 5'); insert into account (acct_id,acct_name) values (6,3,'account 6'); insert into account (acct_id,acct_name) values (7,4,'account 7'); insert into account (acct_id,acct_name) values (8,7,'account 8'); insert into account (acct_id,acct_name) values (9,'account 9'); WITH REcursIVE search_graph(acct_id,depth,path,cyclE) AS ( SELECT g.acct_id,g.parent_id,ARRAY[g.acct_id],false FROM account g union all SELECT g.acct_id,sg.depth + 1,path || g.acct_id,g.acct_id = ANY(path) FROM account g,search_graph sg WHERE g.acct_id = sg.parent_id AND NOT cycle ) SELECT path[1] as Child,parent_id as Parent,path || parent_id as path FROM search_graph ORDER BY path[1],depth;
WITH REcursIVE search_graph AS ( SELECT parent_id,ARRAY[acct_id] AS path FROM account UNION ALL SELECT g.parent_id,sg.path || g.acct_id FROM search_graph sg JOIN account g ON g.acct_id = sg.parent_id WHERE g.acct_id <> ALL(sg.path) ) SELECT path[1] AS child,path[array_upper(path,1)] AS parent,path FROM search_graph ORDER BY path;
>列acct_id,深度,周期只是查询中的噪音.
> WHERE条件必须提前一步退出递归,然后才能从顶级节点的重复条目进入结果.这是你原版中的“一个一个”.
其余的是格式化.
WITH REcursIVE search_graph AS ( SELECT parent_id,ARRAY[acct_id] AS path,acct_id <> parent_id AS keep_going FROM account UNION ALL SELECT g.parent_id,sg.path || g.acct_id,g.acct_id <> g.parent_id FROM search_graph sg JOIN account g ON g.acct_id = sg.parent_id WHERE sg.keep_going ) SELECT path[1] AS child,path FROM search_graph ORDER BY path;
SQL Fiddle.@H_673_23@
注意对于带有修饰符的数据类型(如varchar(5))会出现问题(至少高达pg v9.4),因为数组连接丢失了修饰符,但rCTE坚持完全匹配的类型:
> Surprising results for data types with type modifier
以上是大佬教程为你收集整理的sql – 用于传递闭包的递归查询全部内容,希望文章能够帮你解决sql – 用于传递闭包的递归查询所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。