START WITH ... CONNECT BY 层次查询的用法 --建表语句 drop table automobiles 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,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; --使用子查询 select level, lpad(' ',2*level-1)||part_cname||' '||part_ename as partName from automobiles start with part_id=( select part_id from automobiles where part_cname like '%轴%' ) 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=( select part_id from automobiles where part_cname like '%轴%' ) connect by prior parent_id=part_id order by level; --删除指定的节点 select level, lpad(' ',2*level-1)||part_cname||' '||part_ename as partName from automobiles where part_cname <> '底盘' start with part_id=1 connect by prior parent_id=part_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 parent_id=part_id and part_cname <> '底盘' order by level;
呵呵,以前看书籍的时候做的小例子。不过工作中还没有机会用到start with ....... connect by这个实例呢!
参考start with ... connect by 用法: http://blog.csdn.net/hdhai9451/archive/2009/02/12/3880318.aspx
--建表语句
drop table automobiles
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,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;
--使用子查询
select level,
lpad(' ',2*level-1)||part_cname||' '||part_ename as partName
from automobiles
start with part_id=(
select part_id from automobiles where part_cname like '%轴%'
)
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=(
select part_id from automobiles where part_cname like '%轴%'
)
connect by prior parent_id=part_id
order by level;
--删除指定的节点
select level,
lpad(' ',2*level-1)||part_cname||' '||part_ename as partName
from automobiles
where part_cname <> '底盘'
start with part_id=1
connect by prior parent_id=part_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 parent_id=part_id and part_cname <> '底盘'
order by level;
http://blog.csdn.net/hdhai9451/archive/2009/02/12/3880318.aspx
嗯,不错,对start with...connect by有了更深一层的理解。
google里面没有测试数据以及完全的sql脚本啊!我是个oracle初学者啊,所以2楼的比较适合我啊,3楼的可以在2楼的基础进一步理解啊!不好意思!