使用 WITH RECURSIVE 实现递归查询

• SQL查询中WITH xxx as () 是对一个查询子句做别名，同时数据库会对该子句生成临时表（WITH子句只能被SELECT查询块引用）。

• WITH RECURSIVE 则是一个递归的查询子句，他会把查询出来的结果再次代入到查询子句中继续查询。

创建表并插入数据

create table city
(
  id        int4,
  pid	    int4,
  name  	varchar(50)
);

INSERT INTO "city" ("id", "pid", "name") VALUES (1000, 0, '广东省');
INSERT INTO "city" ("id", "pid", "name") VALUES (1001, 0, '江苏省');
INSERT INTO "city" ("id", "pid", "name") VALUES (1002, 1000, '广州市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1003, 1000, '深圳市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1004, 1000, '佛山市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1005, 1000, '东莞市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1006, 1000, '珠海市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1007, 1000, '中山市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1008, 1000, '惠州市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1009, 1000, '汕头市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1010, 1000, '江门市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1011, 1000, '湛江市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1012, 1000, '肇庆市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1013, 1000, '梅州市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1014, 1000, '茂名市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1015, 1000, '阳江市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1016, 1000, '清远市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1017, 1000, '韶关市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1018, 1000, '揭阳市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1019, 1000, '汕尾市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1020, 1000, '潮州市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1021, 1000, '河源市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1022, 1000, '云浮市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1023, 1001, '南京市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1024, 1001, '苏州市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1025, 1001, '无锡市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1026, 1001, '南通市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1027, 1001, '常州市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1028, 1001, '徐州市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1029, 1001, '盐城市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1030, 1001, '扬州市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1031, 1001, '泰州市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1032, 1001, '镇江市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1033, 1001, '淮安市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1034, 1001, '连云港市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1035, 1001, '宿迁市');
INSERT INTO "city" ("id", "pid", "name") VALUES (1036, 1002, '荔湾区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1037, 1002, '越秀区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1038, 1002, '海珠区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1039, 1002, '天河区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1040, 1002, '白云区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1041, 1002, '黄埔区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1042, 1002, '番禺区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1043, 1002, '花都区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1044, 1002, '萝岗区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1045, 1002, '南沙区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1046, 1002, '增城区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1047, 1002, '从化区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1048, 1024, '姑苏区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1049, 1024, '虎丘区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1050, 1024, '吴中区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1051, 1024, '相城区');
INSERT INTO "city" ("id", "pid", "name") VALUES (1052, 1024, '吴江区');

WITH RECURSIVE 向下递归查询示例

以广东省为根节点，向下查询城市和市区

with recursive c as (
	select c1.id as id,
				 c1.pid as pid,
				 c1.name as name,
				 1::integer as ll	
	from city c1
	where c1.id = 1000
	union all
	select c1.id as id,
				 c1.pid as pid,
				 c1.name as name,
				 c.ll + 1 as ll	
	from c,city c1
	where c1.pid = c.id
) 
select c.id,
			 c.pid,
			 c.name,
			 c.ll	
from c
order by c.id

递归向下查询结果如下图：

二级联动效果查询

with recursive c as (
	select c1.id as id,
				 c1.pid as pid,
				 cast(c1.name as varchar(100)) as name,
				 1::integer as ll	
	from city c1
	where c1.id = 1000
	union all
	select c1.id as id,
				 c1.pid as pid,
				 cast(c.name|| '>' || c1.name as varchar(100)) as name,
				 c.ll + 1 as ll	
	from city c1
	inner join c on c.id = c1.pid
	where c1.pid = c.id
) 
select c.id,
			 c.pid,
			 c.name,
			 c.ll	
from c
order by c.id

查询结果如下图：

以越秀区为节点向上递归查询

with recursive c as (
	select c1.id as id,
				 c1.pid as pid,
				 c1.name as name,
				 1::integer as ll	
	from city c1
	where c1.id = 1037
	union all
	select c1.id as id,
				 c1.pid as pid,
				 c1.name as name,
				 c.ll + 1 as ll	
	from c,city c1
	where c1.id = c.pid
) 
select c.id,
			 c.pid,
			 c.name,
			 c.ll	
from c
order by c.id

递归向上查询结果如下图：