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文件》)