create table(
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' );
select ccid,ltrim(sys_connect_by_path(ccname,'-'),'-') ccname
from t
start with fid='9999'
connect by prior ccid = fid;
--结果
CCID CCNAME-------------------- --------------------------------------------------------------------------------
0001 家用电器
00010001 家用电器-小家电
00010002 家用电器-大家电
000100020001 家用电器-大家电-洗衣机
000100020002 家用电器-大家电-平板电视
000100020003 家用电器-大家电-冰箱
0002 手机数码
00020001 手机数码-手机通讯
00020002 手机数码-手机配件
00020003 手机数码-数码配件
0003 服饰鞋帽
0004 图书影视
12 rows selected