日期:2014-05-17  浏览次数:20825 次

可能涉及递归算法的SQL难题,求高手当头棒喝:嘿!你这呆子!!
Table结构入下图:
  

题目是:
  用一个sql语句统计出各个根区域(隶属地域为-1的即为根区域)的用户累计上网时长。
  

例如:
   重庆的“隶属区域”为-1,所以它是根区域。有两个区(九龙坡和渝中区)隶属于它,而其中的九龙坡下面又有“石桥铺”隶属于它。
   重庆、九龙坡、渝中区和石桥铺这些地区,分别有部分用户,这些用户的上网时长的总和,就是“重庆”的上网时长。

最后的结果:
   北京   6500
   重庆   900

数据库环境:
   oracle

我思考了很久,由于最初没有考虑到地域的关系可能是三级(甚至更多级,只简单的查询所有用户,并对“隶属地域”分组(group by 隶属区域)。这显然是错误的。
如果地域为多级,sql里面应该要包含一个递归的算法?从根区域,遍历所有子区域,得出总和。
但是本人水平太菜,愣是没憋出来这条sql。

这里寻求同行一同讨论。

谢谢大家~~ 哈哈

   

------解决方案--------------------
先用connect by得到父子结构,并用connect_by_root关键字找到每个地域的根地域,
然后和上网时长,用户表关联得到每个上网时长所属的根地域,
然后对根地域做个group by就得到结果了。
下面最后出来的是根地域编号,如果要得到根地域名称,你在外面再套层查询就ok.
  
select t2.根地域编号, sum(t1.上网时长) from
(select 上网时长.上网时长, 地域表.编号 地域编号 from 上网时长
  left join 用户表 on 上网时长.用户编号=用户表.编号
  left join 地域表 on 用户表.所属地区编号=地域表.编号) t1 join
(select  地域表.编号 地域编号, 地域表.名称 地域名称, connect_by_root 地域表.编号 根地域编号 from 地域表
  start with 隶属地域 = -1 connect by prior 编号=隶属地域) t2 on t1.地域编号=t2.地域编号
group by t2.根地域编号;

------解决方案--------------------
with t as (
select c.time,b.areaid  from area a,suser b ,nettime c 
where a.id=b.areaid and b.id=c.userid)
select id,name,(select sum(time) from t where 
t where t.id in (select id from area b start with b.id=a.id 
prior b.sjid=b.id )) counttime
 from area a where a.sjid=-1