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

 1 --递归 每个员工的总销售额=自己的销售额+其下级员工的总销售额
 2 --http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/01c1bb05-e2aa-4055-9299-0a05adf8c73d
 3 use tempdb
 4 go
 5 if not object_id('Tempdb..emp') is null
 6     drop table emp
 7 Go
 8 create table emp
 9 (parent_id int,
10 emp_id int not null,
11 emp_name varchar(10),
12 total_mount int)
13 
14 insert into emp
15 select null,2,'Andrew',200
16 union all
17 select 2,1,'Nancy',100
18 union all
19 select 2,3,'Janet',120
20 union all
21 select 3,4,'Michael',80
22 union all
23 select 1,5,'Robert',50
24 
25 select * from emp order by emp_id
26 
27 create function GetSum(@id int)
28 returns int
29 as
30 begin
31      declare @sum int
32      ;with maco as
33      (
34           select total_mount,emp_id,parent_id from emp where emp_id=@id
35           union all
36           select a.total_mount,a.emp_id,a.parent_id from emp a
37           join maco b on a.parent_id=b.emp_id
38           where a.emp_id<>@id
39      )
40      select @sum=sum(total_mount) from maco
41      return @sum
42 end
43 
44 
45 select *,newsum=dbo.GetSum(emp_id) from emp
46 /*
47 parent_id   emp_id      emp_name   total_mount newsum
48 ----------- ----------- ---------- ----------- -----------
49 NULL        2           Andrew     200         550
50 2           1           Nancy      100         150
51 2           3           Janet      120         200
52 3           4           Michael    80          80
53 1           5           Robert     50          50
54 */