CREATE function [dbo].[GetEmpDepNum](    @ID int)RETURNS @Tree Table (ID [int] IDENTITY (1, 1),PID  Int,FID Int,SN Varchar(150), Name Varchar(150), Num Varchar(150))asbegin declare @MaxNum int,@i int,@f int,@sNnm intInsert @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)    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(100),@i)+‘|‘+CONVERT(varchar(100), @sNnm)        if @sNnm>0         begin           update  @Tree set Num =@sNnm from @Tree where PID=@f         end    SET  @i=@i+1  end--select * from @TreeBcb  order by FID desc,pid --select PID, FID,CASE Num WHEN 0 THEN Name ELSE Name+‘ (‘+Num+‘)‘ END as Name  from @TreeBcb  order by FID desc,pid Returnend GO

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