oralce的递归查询实例

最新推荐文章于 2024-09-05 15:34:51 发布

原创最新推荐文章于 2024-09-05 15:34:51 发布 · 891 阅读

0 ·

CC 4.0 BY-SA版权

oralce 专栏收录该内容

4 篇文章

订阅专栏

--地域表
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;