postgresql中关联多表递归查询，并分组计数、求和

数据表

create table greenSpace
(
    gid  serial,
    type varchar(254), -- 类型，关联类型表code字段
    area numeric -- 面积
);

类型表（父子级关系），最父级的父级编码是-1

create table greenType
(
    code               varchar(10) not null -- 类型编码
        constraint greenType
            primary key,
    name               varchar(20), -- 类型名称
    parentcode         varchar(10) -- 父级编码
);

 递归查询语法结构：

with recursive 名字 as (
    A.初始条件语句(非递归部分)
    
    union [all] 
    
    B.递归部分语句
) [SELECT | INSERT | UPDATE | DELETE]

说明： 前半部分A为初始条件语句，后半部分B为要进行的递归语句

先执行A语句，然后将A语句的结果作为B语句的条件，如果需要对查询结果去重则使用union进行连接，否则使用union all进行连接

需求一：把数据表中数据按type分类，并归类到顶级的父类，统计每个大类的个数

WITH RECURSIVE r AS (
    SELECT d.*, ld.gid
    FROM greenType d
             left join greenSpace ld
                        on d.code = ld.type
    union ALL
    SELECT d.*, r.gid
    FROM greenType d,
         r
    WHERE d.code = r.parentcode
)
SELECT r.name, count(r.gid)
FROM r
where parentcode = '-1'
group by r.name

需求二：把数据表中数据按type分类，并归类到顶级的父类，统计每个大类中所有面积字段和

WITH RECURSIVE r AS (
    SELECT d.*, ld.area
    FROM greenType d
             left join greenSpace ld
                        on d.code = ld.type
    union ALL
    SELECT d.*, r.area
    FROM greenType d,
         r
    WHERE d.code = r.parentcode
)
SELECT r.name, coalesce(sum(r.area),0)
FROM r
where parentcode = '-1'
group by r.name

更多用法待研究……

如有问题，欢迎指出讨论