- 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' );
- 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 图书影视
oracle 部门层级查询
最新推荐文章于 2024-03-11 20:00:09 发布