递归统计人数

with res
as
(
select id, parent_id, isnull(b.number,0) as number from t_labor_team a
left join (
select supplier_id,count(*) as number from t_base_employ where deleted=0 and jobstatus<>1 group by supplier_id
) b on a.id=b.supplier_id
where (deleted=0 or deleted is null) 
),
tree
as
(
select res.id, parent_id, number, convert(varchar(2000),  replicate('0', 10) + '>' +  right(replicate('0', 10) + convert(varchar(10), id), 10)) as path 
    from res 
    where res.parent_id = 0
union all
select res.id, res.parent_id, res.number, convert(varchar(2000),  tree.path + '>' + right(replicate('0', 10) + convert(varchar(10), res.id), 10)) as path 
    from res
    inner join tree on res.parent_id = tree.id
)

select a.id,case when isnull(a.Parent_id,0)>0 then a.Parent_id else deptid end as Parent_id
,name+'('+cast(isnull(b.total,0) as varchar(50))+'人)' as deptname
from t_labor_team a
left join (
select tree.*,  tmp.total
    from tree 
    cross apply (select sum(number) as total from tree t1 where charindex(tree.path, t1.path) > 0) tmp
	) b on a.id=b.id

数据：（所需源数据可在资源中下载：《递归统计人数所需sql文件》）