--参考一下吧 with test as( select '100' id from dual union all select '100001' id from dual union all select '100002' id from dual union all select '100002001' id from dual union all select '100002002' id from dual union all select '200' id from dual union all select '200001' id from dual union all select '200002' id from dual union all select '200002001' id from dual union all select '200002002' id from dual) select level, id, ltrim(sys_connect_by_path(id, '->'),'->') treepath from test connect by prior id = substr(id, 1, length(id) - 3) start with length(id) = 3;
参考: --start with ... connect by 层次查询的用法 --建表语句 create table automobiles( part_id number(5) constraint pk_auto_part_id primary key, parent_id number(5) constraint fk_auto_ppid_ references automobiles(part_id), part_cname varchar2(30) not null, part_ename varchar2(30) not null, mp_cost number(9,2), desribe varchar2(20) ); --插入数据 insert into automobiles values( 1,1,'汽车','mobile',84321.99,'Assembly'); insert into automobiles values( 2,1,'车身','bodywork',19892.99,'Manufacture'); insert into automobiles values( 3,1,'发送机','engine',42128,'Purchase'); insert into automobiles values( 4,1,'附件','attached',15212,'Assembly'); insert into automobiles values( 5,2,'保险杠','bumper',4812.95,'Purchase'); insert into automobiles values( 6,2,'底盘','chassis',12795.11,'Manufacture'); insert into automobiles values( 7,2,'行李箱','Boot',812.11,'Manufacture'); insert into automobiles values( 8,6,'车轮','Wheel',2062.2,'Manufacture'); insert into automobiles values( 9,6,'挡泥板','Mudguard',990,'Manufacture'); insert into automobiles values( 10,8,'轮胎','Tyre',300,'Purchase'); insert into automobiles values( 11,3,'发送机盘','Bonnet',3212,'Manufacture'); insert into automobiles values( 12,3,'活塞','Piston',1112.2,'Manufacture'); insert into automobiles values( 13,3,'汽化器','Carburetter',712.29,'Manufacture'); insert into automobiles values( 14,4,'变速器','Gearbox',5712.25,'Manufacture'); insert into automobiles values( 15,4,'仪表板','Dashboard',538.92,'Manufacture'); insert into automobiles values( 16,14,'制动器','Carburetter',712.29,'Manufacture'); insert into automobiles values( 17,14,'变速杆','Gearshift',2001,'Manufacture'); insert into automobiles values( 18,17,'传动轴','Shaft',1101,'Manufacture'); insert into automobiles values( 19,15,'里程表','Milometer',350.28,'Purchase'); --分层sql脚本语句练习 select level,part_id,parent_id,part_cname,part_ename,mp_cost,desribe from automobiles start with part_id=1 connect by nocycle prior part_id=parent_id order by level; --缩进显示 select level, lpad(' ',2*level-1)||part_cname||' '||part_ename as partName from automobiles start with part_id=1 connect by nocycle prior part_id=parent_id order by level; /* 以下为出错语句,加connect by nocycle --分层sql脚本语句练习 select level,part_id,parent_id,part_cname,part_ename,mp_cost,desribe from automobiles start with part_id=1 connect by prior part_id=parent_id order by level; --缩进显示 select level, lpad(' ',2*level-1)||part_cname||' '||part_ename as partName from automobiles start with part_id=1 connect by prior part_id=parent_id order by level; */-------------------------------------------------------------------------将第一条数据中的parent_id改为null,否则loop循环找parent_id就找不到了!--start with ... connect by 层次查询的用法 --建表语句 create table automobiles( part_id number(5) constraint pk_auto_part_id primary key, parent_id number(5) constraint fk_auto_ppid_ references automobiles(part_id), part_cname varchar2(30) not null, part_ename varchar2(30) not null, mp_cost number(9,2), desribe varchar2(20) ); --插入数据,将parent_id改为null就ok了吧。 insert into automobiles values( 1,null,'汽车','mobile',84321.99,'Assembly'); insert into automobiles values( 2,1,'车身','bodywork',19892.99,'Manufacture'); insert into automobiles values( 3,1,'发送机','engine',42128,'Purchase'); insert into automobiles values( 4,1,'附件','attached',15212,'Assembly'); insert into automobiles values( 5,2,'保险杠','bumper',4812.95,'Purchase'); insert into automobiles values( 6,2,'底盘','chassis',12795.11,'Manufacture'); insert into automobiles values( 7,2,'行李箱','Boot',812.11,'Manufacture'); insert into automobiles values( 8,6,'车轮','Wheel',2062.2,'Manufacture'); insert into automobiles values( 9,6,'挡泥板','Mudguard',990,'Manufacture'); insert into automobiles values( 10,8,'轮胎','Tyre',300,'Purchase'); insert into automobiles values( 11,3,'发送机盘','Bonnet',3212,'Manufacture'); insert into automobiles values( 12,3,'活塞','Piston',1112.2,'Manufacture'); insert into automobiles values( 13,3,'汽化器','Carburetter',712.29,'Manufacture'); insert into automobiles values( 14,4,'变速器','Gearbox',5712.25,'Manufacture'); insert into automobiles values( 15,4,'仪表板','Dashboard',538.92,'Manufacture'); insert into automobiles values( 16,14,'制动器','Carburetter',712.29,'Manufacture'); insert into automobiles values( 17,14,'变速杆','Gearshift',2001,'Manufacture'); insert into automobiles values( 18,17,'传动轴','Shaft',1101,'Manufacture'); insert into automobiles values( 19,15,'里程表','Milometer',350.28,'Purchase'); / --分层sql脚本语句练习 select level,part_id,parent_id,part_cname,part_ename,mp_cost,desribe from automobiles start with part_id=1 connect by prior part_id=parent_id order by level; --缩进显示 select level, lpad(' ',2*level-1)||part_cname||' '||part_ename as partName from automobiles start with part_id=1 connect by prior part_id=parent_id order by level;
with test as(
select '100' id from dual union all
select '100001' id from dual union all
select '100002' id from dual union all
select '100002001' id from dual union all
select '100002002' id from dual union all
select '200' id from dual union all
select '200001' id from dual union all
select '200002' id from dual union all
select '200002001' id from dual union all
select '200002002' id from dual)
select level, id, ltrim(sys_connect_by_path(id, '->'),'->') treepath
from test
connect by prior id = substr(id, 1, length(id) - 3)
start with length(id) = 3;
--start with ... connect by 层次查询的用法
--建表语句
create table automobiles(
part_id number(5)
constraint pk_auto_part_id primary key,
parent_id number(5)
constraint fk_auto_ppid_ references automobiles(part_id),
part_cname varchar2(30) not null,
part_ename varchar2(30) not null,
mp_cost number(9,2),
desribe varchar2(20)
);
--插入数据
insert into automobiles values( 1,1,'汽车','mobile',84321.99,'Assembly');
insert into automobiles values( 2,1,'车身','bodywork',19892.99,'Manufacture');
insert into automobiles values( 3,1,'发送机','engine',42128,'Purchase');
insert into automobiles values( 4,1,'附件','attached',15212,'Assembly');
insert into automobiles values( 5,2,'保险杠','bumper',4812.95,'Purchase');
insert into automobiles values( 6,2,'底盘','chassis',12795.11,'Manufacture');
insert into automobiles values( 7,2,'行李箱','Boot',812.11,'Manufacture');
insert into automobiles values( 8,6,'车轮','Wheel',2062.2,'Manufacture');
insert into automobiles values( 9,6,'挡泥板','Mudguard',990,'Manufacture');
insert into automobiles values( 10,8,'轮胎','Tyre',300,'Purchase');
insert into automobiles values( 11,3,'发送机盘','Bonnet',3212,'Manufacture');
insert into automobiles values( 12,3,'活塞','Piston',1112.2,'Manufacture');
insert into automobiles values( 13,3,'汽化器','Carburetter',712.29,'Manufacture');
insert into automobiles values( 14,4,'变速器','Gearbox',5712.25,'Manufacture');
insert into automobiles values( 15,4,'仪表板','Dashboard',538.92,'Manufacture');
insert into automobiles values( 16,14,'制动器','Carburetter',712.29,'Manufacture');
insert into automobiles values( 17,14,'变速杆','Gearshift',2001,'Manufacture');
insert into automobiles values( 18,17,'传动轴','Shaft',1101,'Manufacture');
insert into automobiles values( 19,15,'里程表','Milometer',350.28,'Purchase');
--分层sql脚本语句练习
select level,part_id,parent_id,part_cname,part_ename,mp_cost,desribe
from automobiles
start with part_id=1
connect by nocycle prior part_id=parent_id
order by level;
--缩进显示
select level,
lpad(' ',2*level-1)||part_cname||' '||part_ename as partName
from automobiles
start with part_id=1
connect by nocycle prior part_id=parent_id
order by level;
/*
以下为出错语句,加connect by nocycle
--分层sql脚本语句练习
select level,part_id,parent_id,part_cname,part_ename,mp_cost,desribe
from automobiles
start with part_id=1
connect by prior part_id=parent_id
order by level;
--缩进显示
select level,
lpad(' ',2*level-1)||part_cname||' '||part_ename as partName
from automobiles
start with part_id=1
connect by prior part_id=parent_id
order by level;
*/-------------------------------------------------------------------------将第一条数据中的parent_id改为null,否则loop循环找parent_id就找不到了!--start with ... connect by 层次查询的用法
--建表语句
create table automobiles(
part_id number(5)
constraint pk_auto_part_id primary key,
parent_id number(5)
constraint fk_auto_ppid_ references automobiles(part_id),
part_cname varchar2(30) not null,
part_ename varchar2(30) not null,
mp_cost number(9,2),
desribe varchar2(20)
);
--插入数据,将parent_id改为null就ok了吧。
insert into automobiles values( 1,null,'汽车','mobile',84321.99,'Assembly');
insert into automobiles values( 2,1,'车身','bodywork',19892.99,'Manufacture');
insert into automobiles values( 3,1,'发送机','engine',42128,'Purchase');
insert into automobiles values( 4,1,'附件','attached',15212,'Assembly');
insert into automobiles values( 5,2,'保险杠','bumper',4812.95,'Purchase');
insert into automobiles values( 6,2,'底盘','chassis',12795.11,'Manufacture');
insert into automobiles values( 7,2,'行李箱','Boot',812.11,'Manufacture');
insert into automobiles values( 8,6,'车轮','Wheel',2062.2,'Manufacture');
insert into automobiles values( 9,6,'挡泥板','Mudguard',990,'Manufacture');
insert into automobiles values( 10,8,'轮胎','Tyre',300,'Purchase');
insert into automobiles values( 11,3,'发送机盘','Bonnet',3212,'Manufacture');
insert into automobiles values( 12,3,'活塞','Piston',1112.2,'Manufacture');
insert into automobiles values( 13,3,'汽化器','Carburetter',712.29,'Manufacture');
insert into automobiles values( 14,4,'变速器','Gearbox',5712.25,'Manufacture');
insert into automobiles values( 15,4,'仪表板','Dashboard',538.92,'Manufacture');
insert into automobiles values( 16,14,'制动器','Carburetter',712.29,'Manufacture');
insert into automobiles values( 17,14,'变速杆','Gearshift',2001,'Manufacture');
insert into automobiles values( 18,17,'传动轴','Shaft',1101,'Manufacture');
insert into automobiles values( 19,15,'里程表','Milometer',350.28,'Purchase');
/
--分层sql脚本语句练习
select level,part_id,parent_id,part_cname,part_ename,mp_cost,desribe
from automobiles
start with part_id=1
connect by prior part_id=parent_id
order by level;
--缩进显示
select level,
lpad(' ',2*level-1)||part_cname||' '||part_ename as partName
from automobiles
start with part_id=1
connect by prior part_id=parent_id
order by level;