CREATE function [dbo].[GetEmpDepNum]

(

    @ID int

)

RETURNS @Tree Table (ID [int] IDENTITY (, ),PID  Int,FID Int,SN Varchar(), Name Varchar(), Num Varchar())

as

begin

declare @MaxNum int,@i int,@f int,@sNnm int

Insert @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 @TreeBcb

SELECT  @MaxNum=Count(*) from @Tree

set @i=

  while (@i<=@MaxNum)

    begin

        select @f=fid from @Tree where ID=@i

        select @sNnm=SUM(CONVERT(int,num)) from @Tree  where FID=(select fid from @Tree where ID=@i )

        --print 's ||'+CONVERT(varchar(),@i)+'|'+CONVERT(varchar(), @sNnm)

        if @sNnm>

         begin

           update  @Tree set Num =@sNnm from @Tree where PID=@f

         end

    SET  @i=@i+

  end

--select * from @TreeBcb  order by FID desc,pid

--select PID, FID,CASE Num WHEN  THEN Name ELSE Name+' ('+Num+')' END as Name  from @TreeBcb  order by FID desc,pid 

Return

end 

GO

select PID, FID,CASE Num WHEN  THEN Name ELSE Name+' ('+Num+')' END as Name  from dbo.GetEmpDepNum()  order by FID ,pid