oracle 层级查询

[robin嘟嘟]

目前有个表：层次ID，层次name， 父id
层次id4位编码：比如0001 下一级就是00010001
如何能够查询出这样子

编码 名称

0001 家用电器
00010001 家用电器-小家电
000100020001 家用电器-小家电-洗衣机

create table T  (
   CCID             varchar2(20)                        not null,
   CCNAME             VARCHAR2(100)                   not null,
   fid              varchar2(20) 
   constraint PK_T primary key (CCID)
);
insert into t values('0001',        '家用电器', '9999' );
insert into t values('00010001',    '小家电',    '0001'  );
insert into t values('00010002',    '大家电',     '0001'  );
insert into t values('000100020001','洗衣机',     '00010002' );
insert into t values('000100020002','平板电视',   '00010002' );
insert into t values('000100020003','冰箱',       '00010002' );
insert into t values('0002',        '手机数码',  '9999'  );
insert into t values('00020001',   '手机通讯',   '0002' );
insert into t values('00020002',    '手机配件',   '0002'  );
insert into t values('00020003',    '数码配件',   '0002'  );
insert into t values('0003',        '服饰鞋帽',  '9999'  );
insert into t values('0004',        '图书影视',  '9999' );

[robin嘟嘟]

怎么没人呢

[xpingping]

select ccid,decode(connect_by_isleaf,1,prior ccname||ccname,ccname) 
from(select ccid, decode(connect_by_isleaf,0,prior ccname||ccname,ccname) ccname,fid 
from t start with fid ='9999' connect by prior ccid=fid)
start with fid ='9999' connect by prior ccid=fid

[心中的彩虹]

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as scott
 
SQL> 
SQL> select ccid,ltrim(sys_connect_by_path(ccname,'-'),'-') ccname
  2   from  t
  3   start with fid='9999'
  4   connect by prior ccid = fid;
 
CCID                 CCNAME
-------------------- --------------------------------------------------------------------------------
0001                 家用电器
00010001             家用电器-小家电
00010002             家用电器-大家电
000100020001         家用电器-大家电-洗衣机
000100020002         家用电器-大家电-平板电视
000100020003         家用电器-大家电-冰箱
0002                 手机数码
00020001             手机数码-手机通讯
00020002             手机数码-手机配件
00020003             手机数码-数码配件
0003                 服饰鞋帽
0004                 图书影视
 
12 rows selected
 
SQL> 

[youqi1984]

[Quote=引用 3 楼 wkc168 的回复:]
SQL code


Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott

SQL>
SQL> select ccid,ltrim(sys_connect_by_path(ccname,'-'),'-') ccname
2 from t
……
[/Quote]正解

[robin嘟嘟]

万分感谢。