Mistj's Digital Garden

Appearance

POSTGRESQL-RECURSIVE

例子 ​

sql
WITH RECURSIVE r AS (  
    SELECT phpg_nbphs.organize_institution.organize_id, organize_institution.organize_name  
    FROM phpg_nbphs.organize_institution  
    WHERE organize_id = 11  
      and invalid = '1'  
    union ALL  
    SELECT phpg_nbphs.organize_institution.organize_id, organize_institution.organize_name  
    FROM phpg_nbphs.organize_institution,  
         r  
    WHERE organize_institution.parent_id = r.organize_id  
      and organize_institution.invalid = '1'  
)  
select * from r;
WITH RECURSIVE r AS (  
    SELECT phpg_nbphs.organize_institution.organize_id, organize_institution.organize_name  
    FROM phpg_nbphs.organize_institution  
    WHERE organize_id = 11  
      and invalid = '1'  
    union ALL  
    SELECT phpg_nbphs.organize_institution.organize_id, organize_institution.organize_name  
    FROM phpg_nbphs.organize_institution,  
         r  
    WHERE organize_institution.parent_id = r.organize_id  
      and organize_institution.invalid = '1'  
)  
select * from r;

这个写的不错 PostgreSQL中RECURSIVE递归查询使用总结

sql
create table document_directories  
(  
    id         bigserial                                          not null  
        constraint document_directories_pk  
            primary key,  
    name       text                                               not null,  
    created_at timestamp with time zone default CURRENT_TIMESTAMP not null,  
    updated_at timestamp with time zone default CURRENT_TIMESTAMP not null,  
    parent_id  bigint                   default 0                 not null  
);  
  
comment on table document_directories is '文档目录';  
  
comment on column document_directories.name is '名称';  
  
comment on column document_directories.parent_id is '父级id';  
  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (1, '中国', '2020-03-28 15:55:27.137439', '2020-03-28 15:55:27.137439', 0);  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (2, '上海', '2020-03-28 15:55:40.894773', '2020-03-28 15:55:40.894773', 1);  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (3, '北京', '2020-03-28 15:55:53.631493', '2020-03-28 15:55:53.631493', 1);  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (4, '南京', '2020-03-28 15:56:05.496985', '2020-03-28 15:56:05.496985', 1);  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (5, '浦东新区', '2020-03-28 15:56:24.824672', '2020-03-28 15:56:24.824672', 2);  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (6, '徐汇区', '2020-03-28 15:56:39.664924', '2020-03-28 15:56:39.664924', 2);  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (7, '漕宝路', '2020-03-28 15:57:14.320631', '2020-03-28 15:57:14.320631', 6);
create table document_directories  
(  
    id         bigserial                                          not null  
        constraint document_directories_pk  
            primary key,  
    name       text                                               not null,  
    created_at timestamp with time zone default CURRENT_TIMESTAMP not null,  
    updated_at timestamp with time zone default CURRENT_TIMESTAMP not null,  
    parent_id  bigint                   default 0                 not null  
);  
  
comment on table document_directories is '文档目录';  
  
comment on column document_directories.name is '名称';  
  
comment on column document_directories.parent_id is '父级id';  
  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (1, '中国', '2020-03-28 15:55:27.137439', '2020-03-28 15:55:27.137439', 0);  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (2, '上海', '2020-03-28 15:55:40.894773', '2020-03-28 15:55:40.894773', 1);  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (3, '北京', '2020-03-28 15:55:53.631493', '2020-03-28 15:55:53.631493', 1);  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (4, '南京', '2020-03-28 15:56:05.496985', '2020-03-28 15:56:05.496985', 1);  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (5, '浦东新区', '2020-03-28 15:56:24.824672', '2020-03-28 15:56:24.824672', 2);  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (6, '徐汇区', '2020-03-28 15:56:39.664924', '2020-03-28 15:56:39.664924', 2);  
INSERT INTO document_directories (id, name, created_at, updated_at, parent_id) VALUES (7, '漕宝路', '2020-03-28 15:57:14.320631', '2020-03-28 15:57:14.320631', 6);

查询,根据名字进行查询

sql
WITH RECURSIVE r AS (  
    SELECT id, name  
    FROM document_directories where id = 1  
    union  
    SELECT x.id, x.name  
    FROM document_directories x,  
         r  
    WHERE x.parent_id = r.id  
) select * from r;


-- 重新拼接了名字
WITH RECURSIVE r AS (  
    SELECT id, name  
    FROM document_directories where id = 1  
    union  
    SELECT x.id, r.name || '-' || x.name  
    FROM document_directories x,  
         r  
    WHERE x.parent_id = r.id  
) select * from r;

-- 结果如下
1,中国
2,中国-上海
3,中国-北京
4,中国-南京
5,中国-上海-浦东新区
6,中国-上海-徐汇区
7,中国-上海-徐汇区-漕宝路
WITH RECURSIVE r AS (  
    SELECT id, name  
    FROM document_directories where id = 1  
    union  
    SELECT x.id, x.name  
    FROM document_directories x,  
         r  
    WHERE x.parent_id = r.id  
) select * from r;


-- 重新拼接了名字
WITH RECURSIVE r AS (  
    SELECT id, name  
    FROM document_directories where id = 1  
    union  
    SELECT x.id, r.name || '-' || x.name  
    FROM document_directories x,  
         r  
    WHERE x.parent_id = r.id  
) select * from r;

-- 结果如下
1,中国
2,中国-上海
3,中国-北京
4,中国-南京
5,中国-上海-浦东新区
6,中国-上海-徐汇区
7,中国-上海-徐汇区-漕宝路

递归限制深度,例如最深只有3级，直接限制住，避免不必要的查询。

sql
WITH RECURSIVE r AS (  
    SELECT id, name, 1 deep  
    FROM document_directories where id = 1  
    union  
    SELECT x.id, r.name || '-' || x.name, r.deep + 1  
    FROM document_directories x,  
         r  
    WHERE x.parent_id = r.id and r.deep < 3  
) select * from r;

1,中国,1
2,中国-上海,2
3,中国-北京,2
4,中国-南京,2
5,中国-上海-浦东新区,3
6,中国-上海-徐汇区,3
WITH RECURSIVE r AS (  
    SELECT id, name, 1 deep  
    FROM document_directories where id = 1  
    union  
    SELECT x.id, r.name || '-' || x.name, r.deep + 1  
    FROM document_directories x,  
         r  
    WHERE x.parent_id = r.id and r.deep < 3  
) select * from r;

1,中国,1
2,中国-上海,2
3,中国-北京,2
4,中国-南京,2
5,中国-上海-浦东新区,3
6,中国-上海-徐汇区,3

说明 ​

流程说明

1、计算非递归项。对UNION（但不对UNION ALL），抛弃重复行。把所有剩余的行包括在递归查询的结果中，并且也把它们放在一个临时的工作表中。
2、只要工作表不为空，重复下列步骤：
a计算递归项，用当前工作表的内容替换递归自引用。对UNION（不是UNION ALL），抛弃重复行以及那些与之前结果行重复的行。将剩下的所有行包括在递归查询的结果中，并且也把它们放在一个临时的中间表中。
b用中间表的内容替换工作表的内容，然后清空中间表。

WITH RECURSIVE 使用限制 ​

1、 如果在recursive term中使用LEFT JOIN，自引用必须在“左”边
2、 如果在recursive term中使用RIGHT JOIN，自引用必须在“右”边
3、 recursive term中不允许使用FULL JOIN
4、 recursive term中不允许使用GROUP BY和HAVING
5、 不允许在recursive term的WHERE语句的子查询中使用CTE的名字
6、 不支持在recursive term中对CTE作aggregation
7、 recursive term中不允许使用ORDER BY
8、 LIMIT / OFFSET不允许在recursive term中使用
9、 FOR UPDATE不可在recursive term中使用
10、 recursive term中SELECT后面不允许出现引用CTE名字的子查询
11、 同时使用多个CTE表达式时，不允许多表达式之间互相访问（支持单向访问）
12、 在recursive term中不允许使用FOR UPDATE

CTE 优缺点 ​

1、 可以使用递归 WITH RECURSIVE，从而实现其它方式无法实现或者不容易实现的查询
2、 当不需要将查询结果被其它独立查询共享时，它比视图更灵活也更轻量
3、 CTE只会被计算一次，且可在主查询中多次使用
4、 CTE可极大提高代码可读性及可维护性
5、 CTE不支持将主查询中where后的限制条件push down到CTE中，而普通的子查询支持