最后通过   substring_index( substring_index( d.path, '.', 2 ), '.', '-1' )  解决的， 我们这path字段里面记录了公司编码、部门编码、车间编码。  级次3到5级，但是第一级是公司，第二级是部门，后面车间有小车间。   我就光把二级部门用 substring_index 函数截取出来了，然后前面字段用count函数统计一下就能求出总人数了。      

sql 的分组统计查询，group by

group by path

----sql 逐级递归汇总

--测试数据

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

递归后的结果

dept.zip