Oracle递归查询

create table zx_test_city(
       id number(10) not null,
       title varchar2(50),
       parent number(10)
);

插入数据构造一个树形结构：

• 中国
  • 北京
  • 上海
    • 浦东新区
      • 唐镇
      • 曹路镇
    • 静安区
  • 杭州
    • 西湖区
• 美国
  • 华盛顿
  • 纽约
• 日本
  • 东京
  • 大阪

-- 国家
insert into zx_test_city(id, title, parent) values(1, '中国',null);
insert into zx_test_city(id, title, parent) values(2, '美国',null);
insert into zx_test_city(id, title, parent) values(3, '日本',null);
-- 城市
insert into zx_test_city(id, title, parent) values(4, '北京',1);
insert into zx_test_city(id, title, parent) values(5, '上海',1);
insert into zx_test_city(id, title, parent) values(6, '杭州',1);
insert into zx_test_city(id, title, parent) values(7, '华盛顿',2);
insert into zx_test_city(id, title, parent) values(8, '纽约',2);
insert into zx_test_city(id, title, parent) values(9, '东京',3);
insert into zx_test_city(id, title, parent) values(10, '大阪',3);
-- 区域
insert into zx_test_city(id, title, parent) values(11, '浦东新区',5);
insert into zx_test_city(id, title, parent) values(12, '静安区',5);
-- 乡镇
insert into zx_test_city(id, title, parent) values(13, '唐镇',11);
insert into zx_test_city(id, title, parent) values(14, '曹路镇',11);

递归查询的语法大致如下：

select * from tablename start with 条件1 connect by prior 条件2 where 条件3;

• 条件1是根节点的限定语句，表示从一棵树的哪个节点开始查找；
• 条件2是连接条件，其中的prior指当前记录，表示当前记录的什么字段和下一条记录的什么字段进行匹配；
• 条件3是过滤语句；

---

如上，条件2是至关重要的，我们假设现在要从“中国”这个节点开始查找，找到其下所有的地区：

select * from zx_test_city tc start with tc.id = 1 connect by prior tc.id = tc.parent

在如上SQL中，从id为1的节点开始查找，找到那些parent字段值是当前记录id值的所有记录；下面的SQL在逻辑上是等同的：

select * from zx_test_city tc start with tc.id = 1 connect by tc.parent = prior tc.id

所以，prior在哪个字段的前面出现，就代表那个字段是当前记录的字段，另外一个字段就是下一条数据的字段；

---

上面演示的是从上往下的查找，我们还可以从下往上的查找，比如，查找“曹路镇”的所有父级元素：

select * from zx_test_city tc start with tc.id = 14 connect by prior tc.parent = tc.id

---

如果我们只想展示乡镇，而不要城市和区信息怎么办呢？

我们需要给zx_test_city加一个字段，来标识每一条记录的级别：

alter table zx_test_city add area_level varchar(10); 
update zx_test_city tc set tc.area_level = 'country' where tc.id in (1,2,3);
update zx_test_city tc set tc.area_level = 'city' where tc.id in (4,5,6,7,8,9,10);
update zx_test_city tc set tc.area_level = 'area' where tc.id in (11,12);
update zx_test_city tc set tc.area_level = 'town' where tc.id in (13,14);

然后执行如下语句：

select * from (select * from zx_test_city tc start with tc.id = 1 connect by prior tc.id = tc.parent)  where area_level = 'town';