递归？

 参考下面的修改   

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

表里面有数据存入时间吗，根据这个时间判断