笔记217 递归 每个员工的总销售额=自己的销售额+其下级员工的总销售额

笔记217 递归 每个员工的总销售额=自己的销售额+其下级员工的总销售额

--递归 每个员工的总销售额=自己的销售额+其下级员工的总销售额
--http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/01c1bb05-e2aa-4055-9299-0a05adf8c73d
use tempdb
go
if not object_id('Tempdb..emp') is null
    drop table emp
Go
create table emp
(parent_id int,
emp_id int not null,
emp_name varchar(10),
total_mount int)

insert into emp
select null,2,'Andrew',200
union all
select 2,1,'Nancy',100
union all
select 2,3,'Janet',120
union all
select 3,4,'Michael',80
union all
select 1,5,'Robert',50

select * from emp order by emp_id

create function GetSum(@id int)
returns int
as
begin
     declare @sum int
     ;with maco as
     (
          select total_mount,emp_id,parent_id from emp where emp_id=@id
          union all
          select a.total_mount,a.emp_id,a.parent_id from emp a
          join maco b on a.parent_id=b.emp_id
          where a.emp_id<>@id
     )
     select @sum=sum(total_mount) from maco
     return @sum
end


select *,newsum=dbo.GetSum(emp_id) from emp
/*
parent_id   emp_id      emp_name   total_mount newsum
----------- ----------- ---------- ----------- -----------
NULL        2           Andrew     200         550
2           1           Nancy      100         150
2           3           Janet      120         200
3           4           Michael    80          80
1           5           Robert     50          50
*/