oralce的递归查询实例

--地域表
create table lilin_placeinfo
(
   pid int primary key not null, --地域编号
   pname varchar2(50),--地域名称
   ppid int   --隶属地域   
);
 
create  sequence lilin_placeinfo_pid
increment by 1
start with 1
maxvalue 99999999999;

insert into lilin_placeinfo values(lilin_placeinfo_pid.Nextval , '北京',-1);
insert into lilin_placeinfo values(lilin_placeinfo_pid.Nextval , '海淀区',1);
insert into lilin_placeinfo values(lilin_placeinfo_pid.Nextval , '朝阳区',1);
insert into lilin_placeinfo values(lilin_placeinfo_pid.Nextval , '重庆',-1);
insert into lilin_placeinfo values(lilin_placeinfo_pid.Nextval , '九龙坡',4);
insert into lilin_placeinfo values(lilin_placeinfo_pid.Nextval , '渝中区',4);
insert into lilin_placeinfo values(lilin_placeinfo_pid.Nextval , '石桥铺',5);
select * from lilin_placeinfo;

--用户表
create table lilin_userinfo
(
   userid int primary key not null, --用户编号
   username varchar2(50),--用户
   pid int  references lilin_placeinfo(pid) --所属地区区域    
);
 
create  sequence lilin_userinfo_userid
increment by 1
start with 1
maxvalue 99999999999;

insert into lilin_userinfo values(lilin_userinfo_userid.Nextval,'用户1',2);
insert into lilin_userinfo values(lilin_userinfo_userid.Nextval,'用户2',2);
insert into lilin_userinfo values(lilin_userinfo_userid.Nextval,'用户3',3);
insert into lilin_userinfo values(lilin_userinfo_userid.Nextval,'用户4',4);
insert into lilin_userinfo values(lilin_userinfo_userid.Nextval,'用户5',5);
insert into lilin_userinfo values(lilin_userinfo_userid.Nextval,'用户6',6);
insert into lilin_userinfo values(lilin_userinfo_userid.Nextval,'用户7',5);
insert into lilin_userinfo values(lilin_userinfo_userid.Nextval,'用户8',7);

select * from lilin_userinfo;

--上网时长
create table lilin_timeinfo
(
   tid int primary key ,--编号
   userid int references lilin_userinfo(userid), -- yonghu编号
   alltime int --上网时长
);
create  sequence lilin_timeinfo_tid
increment by 1
start with 1
maxvalue 99999999999;

insert into lilin_timeinfo values(lilin_timeinfo_tid.Nextval,1,3000);
insert into lilin_timeinfo values(lilin_timeinfo_tid.Nextval,1,1000);
insert into lilin_timeinfo values(lilin_timeinfo_tid.Nextval,2,500);
insert into lilin_timeinfo values(lilin_timeinfo_tid.Nextval,3,2000);
insert into lilin_timeinfo values(lilin_timeinfo_tid.Nextval,4,400);
insert into lilin_timeinfo values(lilin_timeinfo_tid.Nextval,8,500);

--用sql语句统计出指定区域的用户累计上网时长
select  sum(t.alltime) from lilin_timeinfo t join lilin_userinfo u on u.userid  = t.userid join lilin_placeinfo p on p.pid = u.pid
where p.pid in  ( select pid from lilin_placeinfo start with  pid = (select pid from lilin_placeinfo where pname = 'beijing')  connect by prior  pid =ppid )

--用sql统计出各个根区域(ppid为-1的)的用户累计上网时间 
select p.pname,(select  sum(tt.alltime) from lilin_timeinfo tt join lilin_userinfo uu on uu.userid  = tt.userid join lilin_placeinfo pp
on pp.pid = uu.pid where pp.pid in  ( select pid from lilin_placeinfo start with  pid = (select pid from lilin_placeinfo where pname = p.pname)
connect by prior  pid =ppid ) )  from lilin_placeinfo p   where p.ppid = -1   group by p.pname

 
commit;