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

（编辑：湖南网）

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