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;

解决方法

您可以在几个地方进行简化(假设acct_id和parent_id为非NULL)：

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

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

解决方法

猜你在找的MsSQL相关文章