一个关于累加工资的T-SQL语句
今天在ITPUB看到一个人问的语句问题:http://www.itpub.net/thread-1734957-1-1.html
问题如下:
table:emp
parent_id emp_id emp_name total_amout
NULL 2 Andrew 200
2 1 Nancy 100
2 3 Janet 120
3 4 Michael 80
1 5 Robert 50
每个员工的总销售额=自己的销售额+其下级员工的总销售额,
比如:
Andrew = 200_100_120_80_50=550
Nancy = 100+50=150
Janet = 120+80=200
Michael = 80
Robert = 50
这个用SQL怎样可以查询得到,请教一下大家???
我用递归+游标实现了一下,总感觉应该有更好的实现方式,下面是我的实现方式:
WITH recur(parent_id , emp_id , emp_name , total_amout,tlevel) as ( select parent_id , emp_id , emp_name , total_amout,0 as tlevel from emp as b where parent_id is NULL UNION all SELECT a.parent_id , a.emp_id , a.emp_name , a.total_amout,b.tlevel+1 from emp as a INNER join recur b ON a.parent_id=b.emp_id ) select * INTO emp_ext from recur DECLARE @level int DECLARE @temp int select @level=max(tlevel) FROM emp_ext while(@level>0) begin declare cursor_t cursor local static read_only forward_only for select emp_id from emp_ext where tlevel=@level-1 open cursor_t FETCH NEXT FROM cursor_t into @temp WHILE @@FETCH_STATUS=0 BEGIN print @level update emp_ext set total_amout=(SELECT sum(total_amout) from emp_ext where parent_id=@temp)+(SELECT total_amout from emp_ext where emp_id=@temp) where emp_id=@temp FETCH NEXT FROM cursor_t into @temp END set @level=@level-1 close cursor_t DEALLOCATE cursor_t end
为了便于大家测试,我把生成表和数据的代码也复制如下:
CREATE table emp( parent_id int, emp_id int, emp_name varchar(50), total_amout int ) INSERT into emp(parent_id , emp_id , emp_name , total_amout) VALUES (NULL , 2 , 'Andrew' , 200) INSERT into emp(parent_id , emp_id , emp_name , total_amout) VALUES (2 , 1 , 'Nancy', 100) INSERT into emp(parent_id , emp_id , emp_name , total_amout) VALUES (2 , 3 , 'Janet' , 120) INSERT into emp(parent_id , emp_id , emp_name , total_amout) VALUES (3 , 4 , 'Michael' , 80) INSERT into emp(parent_id , emp_id , emp_name , total_amout) VALUES (1 , 5 , 'Robert' , 50)
大家谁有更好的实现办法,不妨在这讨论一下。