sql server 递归汇总 按指定层级汇总

每个地区递归层级可能不一致,数据表(table)存放最小层级地区 area --地区层级表 id name f_id leve 1 中国 0 1 2 湖北 1 2 3 武汉 2 3 ...

 

--测试数据
with area(id,"name",f_id,leve) as (
  select  1,'中国',0,1 union all
  select  2,'湖北',1,2 union all
  select  3,'武汉',2,3 union all
  select  4,'云贵',1,2 union all
  select  5,'云南',4,3 union all
  select  6,'贵阳',4,3 union all
  select  7,'云南子区',5,4 union all
  select  8,'贵阳子区',6,4 union all
  select  9,'蔡甸',2,3
), "table"(id,area_id,"money") as (
  select  1,3,10 union all
  select  2,9,5 union all
  select  3,7,20 union all
  select  4,8,30
)
--使用cte递归求出每个节点的路径
,t(id,f_id,"name","level",fullpath) as (
  select a.id,a.f_id,a."name",a.leve,cast(a.id as varchar(max))
  from area a
  where a.leve=1
  union all
  select b.id,b.f_id,b."name",b.leve,t.fullpath+'->'+cast(b.id as varchar(max))
  from area b
  inner join t on t.id=b.f_id
)
--汇总统计每个节点的金额
select t.id,t."name",t."level",sum(c."money") as "money"
from t
inner join t t1 on charindex(t.fullpath,t1.fullpath)=1
inner join "table" c on c.area_id=t1.id
group by t.id,t."name",t."level"
having t."level"=2 --筛选出第二层级
order by t.id

结果：

--语句设计的很好，参考一下

转：http://zhidao.baidu.com/link?url=Wevi5LnFNIn66I60bjk7_x8yaTfD2-acH37Zhgmp_5drupcAQ30DciqpG6Ypo7xPaSHInqfItrAMUx1srOuvpT8XjMXvYlzXEV6X8bvbUnW