create table bicycle
(
part_id number(5) ,
parent_id number(5) constraint fk_bicycle_pid references bicycle(part_id),
part_name varchar2(30) not null,
mp_cost number(9,2),
describe varchar2(30),
constraint pk_bicycle_part_id primary key (part_id)
);
insert into bicycle values(1,null,'自行车',379.28,'装配');
insert into bicycle values(2,1,'导向系统',101.11,'制造');
insert into bicycle values(3,1,'驱动系统',159.11,'制造');
insert into bicycle values(4,1,'其他部分',118.11,'制造和采购');
insert into bicycle values(5,2,'车把',37.11,'制造');
insert into bicycle values(6,2,'前叉',24.11,'制造');
insert into bicycle values(7,2,'前轴',19.11,'制造');
insert into bicycle values(8,2,'前轮',19.11,'制造');
insert into bicycle values(9,7,'前轴棍',8.11,'制造');
insert into bicycle values(10,7,'前轴身',4.11,'制造');
insert into bicycle values(11,7,'前轴碗',6.11,'制造');
insert into bicycle values(12,10,'前轴管',1.11,'制造');
insert into bicycle values(13,10,'前轴盘',3.11,'制造');
select level,t.* from bicycle t
start with t.part_id=1
connect by prior part_id=parent_id
(
part_id number(5) ,
parent_id number(5) constraint fk_bicycle_pid references bicycle(part_id),
part_name varchar2(30) not null,
mp_cost number(9,2),
describe varchar2(30),
constraint pk_bicycle_part_id primary key (part_id)
);
insert into bicycle values(1,null,'自行车',379.28,'装配');
insert into bicycle values(2,1,'导向系统',101.11,'制造');
insert into bicycle values(3,1,'驱动系统',159.11,'制造');
insert into bicycle values(4,1,'其他部分',118.11,'制造和采购');
insert into bicycle values(5,2,'车把',37.11,'制造');
insert into bicycle values(6,2,'前叉',24.11,'制造');
insert into bicycle values(7,2,'前轴',19.11,'制造');
insert into bicycle values(8,2,'前轮',19.11,'制造');
insert into bicycle values(9,7,'前轴棍',8.11,'制造');
insert into bicycle values(10,7,'前轴身',4.11,'制造');
insert into bicycle values(11,7,'前轴碗',6.11,'制造');
insert into bicycle values(12,10,'前轴管',1.11,'制造');
insert into bicycle values(13,10,'前轴盘',3.11,'制造');
select level,t.* from bicycle t
start with t.part_id=1
connect by prior part_id=parent_id
order by level;
查询的事包括子辈的和孙子辈的所有直属节点;
select level,t.* from bicycle t
start with t.part_id=1
connect by part_id=prior parent_id
order by level;
如果查找的是节点的直属父节点,也是不用用到树型查询的。