数据表
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
更多用法待研究……
如有问题,欢迎指出讨论