--学会变通啊
with cte 
as (
select * from tb
union all
select a.Name,b.ParentName,a.PersonalSales,a.Rate from tb a join cte b 
on a.ParentName =b.Name)
,cte1
as
(select ISNULL(ParentName,name) name, 
name as ParentName,
case when ParentName is null then PersonalSales else null end PersonalSales,
PersonalSales as TotalSales,
PersonalSales*rate as TotalSales1,
case when ISNULL(ParentName,name)=name then SUM(PersonalSales) over (partition by ISNULL(ParentName,name)) else  PersonalSales end TotalSales2,
Rate,
case when ParentName is null then Rate else null end rate1
 from cte)
,cte2
as
(select a.name,a.ParentName,a.PersonalSales,a.TotalSales,a.TotalSales1,
cast(ISNULL(b.TotalSales2*a.Rate,a.TotalSales2*a.Rate) as int) TotalSales2,a.Rate,a.rate1
 from cte1 a left join cte1 b
on a.ParentName=b.name and b.name=b.ParentName)


select name,SUM(PersonalSales) PersonalSales,SUM(TotalSales) TotalSales,
SUM(TotalSales)*max(Rate1) TotalIncome,
--SUM(TotalSales2)
[color=#FF0000]SUM(case when PersonalSales is null then -TotalSales2 else TotalSales2 end) [/color]
from cte2
group by name

name       PersonalSales TotalSales  TotalIncome                             
---------- ------------- ----------- --------------------------------------- -----------
A          2000          4400        2200.0                                  1520
B          1000          1000        200.0                                   200
C          800           1400        420.0                                   360
D          600           600         60.0                                    60
警告: 聚合或其他 SET 操作消除了 Null 值。

(4 行受影响)