SQL 由人员汇总到部门树递归合计总数函数
时间：2021-07-01 10:21:17
帮助过：2人阅读
int
)
RETURNS @Tree Table (ID [int] IDENTITY (
1,
1),PID Int,FID Int,SN Varchar(
150), Name Varchar(
150), Num Varchar(
150))
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=
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
Return
end
GO
2、调用
select PID, FID,CASE Num WHEN 0 THEN Name ELSE Name+‘ (‘+Num+‘)‘ END as Name from dbo.GetEmpDepNum(0) order by FID ,pid
SQL 由人员汇总到部门树递归合计总数函数
标签：
