假设有表Test
Name , ParentName , PersonalSales , Rate
A ,NULL,2000,0.5
B,A,1000,0.2
C,A,800,0.3
D,C,600,0.1
结果
Name,PersonalSales,TotalSales,TotalIncome,PersonalIncome
A, 2000, 4400, 4400*0.5, 2200-200-420-60
B, 1000, 1000, 1000*0.2, 200
C, 800, 1400, 1400*0.3, 420-60
D, 600, 600, 600*0.1, 60
注：PersonalSales：个人销售额
TotalSales:总网销售额
TotalIncome:总提成
PersonalIncome:个人收入
------解决方案--------------------
- SQL code
--学会变通啊with cte as (select * from tbunion allselect a.Name,b.ParentName,a.PersonalSales,a.Rate from tb a join cte b on a.ParentName =b.Name),cte1as(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),cte2as(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 bon 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 cte2group by namename PersonalSales TotalSales TotalIncome ---------- ------------- ----------- --------------------------------------- -----------A 2000 4400 2200.0 1520B 1000 1000 200.0 200C 800 1400 420.0 360D 600 600 60.0 60警告: 聚合或其他 SET 操作消除了 Null 值。(4 行受影响)