oracle 层级 汇总,oracle 部门层级查询

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