--创建地区表
CREATE TABLE region(
id serial PRIMARY KEY, --主键，自增
rc varchar(20) NOT NULL, --地区code
pc varchar(20) NOT NULL, --父地区code
nm varchar(20) --名称
);
------添加测试数据
--0
|--110000，北京
|--110100，西城
|--440000，湖北
|--440100，武汉
|--440101，武昌
|--440200，黄石
|--440201，黄石港
|--440202，下陆
-----------------
insert into region(rc,pc,nm) values
('110000','0','北京'),
('110100','110000','北京-西城'),
('440000','0','湖北'),
('440100','440000','湖北-武汉'),
('440101','440100','湖北-武汉-武昌')
('440200','440000','湖北-黄石'),
('440201','440200','湖北-黄石-黄石港'),
('440202','440200','湖北-黄石-下陆');
- postgres
- 设置最大深度为2:
WITH RECURSIVE tmp(id,rc,pc,nm,deep) as
(
select id,rc,pc,nm,1 as deep from region where id = 3
union all
select t1.id,t1.rc,t1.pc,t1.nm,t2.deep + 1
from region t1,tmp t2
where t2.deep < 2 --设置最大深度为2
and t1.pc = t2.rc
)
select * from tmp;
最大深度为2
- 设置最大深度为3:
WITH RECURSIVE tmp(id,rc,pc,nm,deep) as
(
select id,rc,pc,nm,1 as deep from region where id = 3
union all
select t1.id,t1.rc,t1.pc,t1.nm,t2.deep + 1
from region t1,tmp t2
where t2.deep < 3 --设置最大深度为3
and t1.pc = t2.rc
)
select * from tmp;
最大深度为3
