~~~!!!比较难的SQL!!!~~~

[hxm20003]

create table #t0
(
EmployeeID int,
parent_employeeID int,
sales decimal(18,2)
)
insert into #t0 values (1,2,100)
insert into #t0 values (2,4,200)
insert into #t0 values (3,5,60)
insert into #t0 values (4,6,20)
insert into #t0 values (5,0,60)
insert into #t0 values (6,8,100)
insert into #t0 values (7,8,20)
insert into #t0 values (8,0,30)
insert into #t0 values (9,0,10)

EmployeeID parent_employeeID sales
1 2 100
2 4 200
3 5 60
4 6 20
5 0 60
6 8 100
7 8 20
8 0 30
9 0 10

结果如下：
EmployeeID N(层次) sales_amount
1 5 100
2 4 250 (100 * 0.5 + 200)
3 2 60
4 3 145 (100 * 0.5 + 200) * 0.5 + 20
5 1 60
6 2 172.50 ((100 * 0.5 + 200) * 0.5 + 20) * 0.5 + 100
7 2 20
8 1 126.25 (((100 * 0.5 + 200) * 0.5 + 20) * 0.5 + 100 + 20) * 0.5 + 30
9 2 10

规则如下：
如果EmployeeID有下级的，那么自己的销售额再加上下级的sales*0.5
哪位能给出最佳SQL？

[子陌红尘]

先给出一个可行方案：
-----------------------------------------------------------------------------------------------------------------
declare @t0 table(EmployeeID int,Parent_employeeID int,sales decimal(18,2))
insert into @t0 values (1,2,100)
insert into @t0 values (2,4,200)
insert into @t0 values (3,5,60)
insert into @t0 values (4,6,20)
insert into @t0 values (5,0,60)
insert into @t0 values (6,8,100)
insert into @t0 values (7,8,20)
insert into @t0 values (8,0,30)
insert into @t0 values (9,0,10)

declare @t1 table(EmployeeID int,Parent_employeeID int,sales_amount decimal(18,2),level int)
declare @level int
set @level=1
insert into @t1 select *,@level from @t0 where parent_employeeID=0
while @@rowcount<>0
begin
set @level=@level+1
insert into @t1 select a.*,@level from @t0 a,@t1 b where a.parent_employeeID=b.EmployeeID and b.level=@level-1
end

while @level>0
begin
update a set sales_amount=a.sales_amount+isnull((select sum(sales_amount*0.5) from @t1 where parent_employeeID=a.EmployeeID),0)
from @t1 a where level=@level

set @level=@level-1
end

select EmployeeID,Level,sales_amount from @t1 order by EmployeeID

/*
EmployeeID Level sales_amount
----------- ----------- --------------------
1 5 100.00
2 4 250.00
3 2 60.00
4 3 145.00
5 1 90.00
6 2 172.50
7 2 20.00
8 1 126.25
9 1 10.00
*/