SQL aggregated by person to department tree recursive total total function

1, the total number of people calculated, in the department tree (trees) by structure (master parent binding)

CREATE function [dbo]. [Getempdepnum] (@IDint) RETURNS @Tree Table (ID [int] IDENTITY (1,1), PID Int,fid int,sn Varchar ( Max), Name Varchar ( Max), Num Varchar ( Max)) asBEGIN DECLARE @MaxNumint, @iint, @fint, @sNnmintInsert @Tree SELECT c1.pid,c1.fid,c1.sn,c1. Name, (SELECT COUNT (*) from Dbo.tbemployee C2 WHERE C2. MID =c1.pid) as Snum from Tbdeplist C1 order by FID Desc,pid--Select* from@TreeBcbSELECT @MaxNum=count (*) from@TreeSet@i=1   while(@i<=@MaxNum) BeginSelect@f=fid from@TreewhereId=@iSelect@sNnm =sum (CONVERT (int, num)) from@TreewhereFid= (SelectFid from@TreewhereId=@i)--print'S | |'+convert (varchar ( -), @i) +'|'+convert (varchar ( -), @sNnm)if@sNnm >0Begin update @TreeSetNum [email protected] from@TreewherePid=@f End SET @i[Email protected]+1End--Select* from@TreeBcb ORDER by FID Desc,pid--SelectPID, Fid,case Num when0Then Name ELSE name+' ('+num+')'END asName from@TreeBcb ORDER by FID Desc,pid returnend GO

2. Call

Select 0 then Name ELSE name+'  ('+num+')'as Name from   dbo. Getempdepnum (0)  

SQL is aggregated by people into the departmental tree recursive total totals function