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

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

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

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

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