sql – 用于传递闭包的递归查询

我创建了一个简单的例子来说明PostgreSQL中使用递归查询的传递闭包.

但是,我的递归查询有些问题.我不熟悉语法,所以这个请求可能完全是我的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.

注意对于带有修饰符的数据类型(如varchar(5))会出现问题(至少高达pg v9.4),因为数组连接丢失了修饰符,但rCTE坚持完全匹配的类型：

> Surprising results for data types with type modifier

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!