笔记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 */
