SQL树结构 汇总（有点小难度）

Question

本帖最后由 IFocusYou 于 2012-09-20 15:17:32 编辑

Answer 1

A,B,C,D为树状结构，
A的总网销售额即A的所有子节点的PersonalSales之和，即：A.PersonalSales + B.PersonalSales+C.PersonalSales+D.PersonalSales 2000+1000+800+600=4400
同理，B,C,D的总网销售额也是如此。总提成:比较简单，就是乘以各自的Rate个人收入:就是自已的总提成减去所有的子节点的总提成

Answer 2

是的，最后PersonalIncome的计算我没做出来

Answer 3

CREATE TABLE [dbo].[Test](
[ID] [int] NULL,
[ParentID] [int] NULL,
[Amount] [int] NULL,
[Rate] [decimal](18, 4) NULL
) ON [PRIMARY]1 0 2000 0.5000
2 1 1000 0.2000
3 1 800 0.3000
4 3 600 0.1000; with t as
(
SELECT * FROM TEST 
union all
select b.id,b.[ParentID],t.[Amount],t.rate from TEST as b join t on b.id=t.ParentID
) 
select *,TotalAmount*Rate as TotalIncome from (
SELECT * FROM TEST as a cross apply(select sum([Amount]) as TotalAmount  from t where a.id=t.id) app
) as T1最后一步没有实现

Answer 4

--好久没写递归的了，忘的差不多了，其实难就难在PersonalIncome
其实应该可以更精简的，先凑活着用着吧create table tb (name nvarchar(10),ParentName nvarchar(10),PersonalSales int,Rate decimal(18,1))insert into tb values ('A' ,NULL,2000,0.5)
insert into tb values ('B','A',1000,0.2)
insert into tb values ('C','A',800,0.3)
insert into tb values ('D','C',600,0.1)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,
cast(SUM(TotalSales) as nvarchar(10))+'*'+cast(max(Rate1) as nvarchar(10)) TotalIncome,
PersonalIncome=STUFF((
select top 100 percent '-'+cast(TotalSales2 as nvarchar(10))
from 
cte2 a 
where a.name=t.name order by ParentName for xml path('')),1,1,'')
from cte2 t
group by namename       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
警告: 聚合或其他 SET 操作消除了 Null 值。(4 行受影响)

Answer 5

bluesmiler是正确的TotalIncome，PersonalIncome两个字段是decimal类型的，不是字符串型的。我写成那样就是为了显示如何计算的。我再修改一下，看看能否达到目的

Answer 6

最后PersonalIncome如何实现的，能不能帮改为数字的求和，而是字符串的连接！

Answer 7

--那就更简单了
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)
 from cte2
group by name

Answer 8

数据不正确，最后一列PersonalIncome应该是1520
是这样算出来的(2200-200-420-60)

Answer 9

--学会变通啊
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)
SUM(case when PersonalSales is null then -TotalSales2 else TotalSales2 end) 
from cte2
group by namename       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 行受影响)

Answer 10

呵呵。我也想变通的，关键是没理解你的SQL结贴