我创建了一个简单的例子来说明PostgreSQL中使用递归查询的传递闭包。
但是,我的递归查询有问题。我还不熟悉语法,所以这个请求可能完全不适合我,为此我提前道歉。如果运行查询,您将看到节点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, parent_id, acct_name) values (2,1,'account 2');
insert into account (acct_id, parent_id, acct_name) values (3,1,'account 3');
insert into account (acct_id, parent_id, acct_name) values (4,2,'account 4');
insert into account (acct_id, parent_id, acct_name) values (5,2,'account 5');
insert into account (acct_id, parent_id, acct_name) values (6,3,'account 6');
insert into account (acct_id, parent_id, acct_name) values (7,4,'account 7');
insert into account (acct_id, parent_id, acct_name) values (8,7,'account 8');
insert into account (acct_id, parent_id, acct_name) values (9,7,'account 9');

WITH RECURSIVE search_graph(acct_id, parent_id, depth, path, cycle) AS (
        SELECT g.acct_id, g.parent_id, 1,
          ARRAY[g.acct_id],
          false
        FROM account g
      UNION ALL
        SELECT g.acct_id, g.parent_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_idparent_idNOT 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_iddepthcycle只是查询中的噪声。
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[array_upper(path,1)] AS parent
     , path
FROM   search_graph
ORDER  BY path;

SQL Fiddle.
注意,对于带有修饰符(如varchar(5))的数据类型(至少pg v9.4)会有问题,因为数组连接会丢失修饰符,但rCTE坚持类型完全匹配:
Surprising results for data types with type modifier

09-16 23:19
查看更多