PostgreSql递归查询(WITH RECURSIVE)

一、背景

        数据库中存在父子关系的数据，如果要查它及所以子记录，或者查它及所有父记录数据，用递归查询再合适不过了。通常我们的分级加载就可以用这个来实现。以地区表为例来说明

二、建表、造数据

CREATE TABLE place
(
  id serial NOT NULL,                    --主键
  name character varying(200),           --地区名
  parent_id bigint,                      --外键  父地区
  CONSTRAINT place_pkey PRIMARY KEY (id) --主键约束
);
insert into place(id,name,parent_id) values (1,'江苏省',0);
insert into place(id,name,parent_id) values (101,'南京市',1);
insert into place(id,name,parent_id) values (102,'无锡市',1);
insert into place(id,name,parent_id) values (103,'徐州市',1);
insert into place(id,name,parent_id) values (104,'常州市',1);
insert into place(id,name,parent_id) values (105,'苏州市',1);
insert into place(id,name,parent_id) values (1051,'昆山市',105);
insert into place(id,name,parent_id) values (1052,'张家港市',105);
insert into place(id,name,parent_id) values (1053,'常熟市',105);
insert into place(id,name,parent_id) values (1054,'太仓市',105);

 全表数据长这样：

三、向下递归

这里查询江苏省及其下所有市县：

WITH RECURSIVE r AS (
     SELECT * FROM place WHERE id = 1
     union   ALL
     SELECT place.* FROM place, r WHERE place.parent_id = r.id
)
SELECT * FROM r ORDER BY id;

结果：

 

四、向上递归

查询最强县级市昆山市及以上记录：

WITH RECURSIVE r AS (
     SELECT * FROM place WHERE id = 1051
     union   ALL
     SELECT place.* FROM place, r WHERE place.id = r.parent_id
)
SELECT * FROM r ORDER BY id;

 结果：

五、参考

http://www.2cto.com/database/201309/242797.html