一道层次查询题的两种解法

--建表脚本如下：
create table area
(
  id         number not null,  --pk
  areaid     number not null,  --uk
  areaname   varchar2(80) not null,
  areaparent number not null,
  areastatus char(1) default 1 not null
);

--模拟数据如下：
insert into area values (1  ,110000,'北京市  ',0     ,'1');
insert into area values (35 ,110100,'市辖区  ',110000,'1');
insert into area values (36 ,110200,'县      ',110000,'1');
insert into area values (380,110101,'东城区  ',110100,'1');
insert into area values (381,110102,'西城区  ',110100,'1');
insert into area values (382,110103,'崇文区  ',110100,'1');
insert into area values (387,110108,'海淀区  ',110100,'1');
insert into area values (395,110117,'平谷区  ',110100,'1');
insert into area values (396,110228,'密云县  ',110200,'1');
insert into area values (397,110229,'延庆县  ',110200,'1');

--希望得到如下结果：
SQL> select ..... from area where ..... areaid=110108
col
----------------------------
北京市  市辖区  海淀区

SQL> select ..... from area where ..... areaid=110228
col
----------------------------
北京市  县      密云县

解法一:
SQL> select replace(wm_concat(areaname),',','') col from
  2  (select areaname from area
  3     start with areaid = 110108
  4     connect by  areaid =  prior  areaparent
  5    order by level desc
  6    );
 
col
--------------------------------------------------------------------------------
北京市  市辖区  海淀区
 
解法二:
SQL> select replace(sys_connect_by_path(areaname,'->'),'->') col from area
  2   where areaid=110108
  3  start with areaparent =0
  4  connect by prior areaid=areaparent;
 
col
--------------------------------------------------------------------------------
北京市  市辖区  海淀区

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23740698/viewspace-663722/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23740698/viewspace-663722/