SQL递归查询

--创建地区表
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