我想要这个总价格(36000)的传销的份额计算.

问题描述

TreeTable
ID名称PID价格
1100 0 3000.00
2 101 1 3000.00
3 102 1 3000.00
4 103 1 3000.00
5 104 2 3000.00
6 105 3 3000.00
7 106 4 3000.00
8 107 5 3000.00
9 108 6 3000.00
10 109 7 3000.00
11110 8 3000.00
12 111 9 3000.00

创建表TreeTable(ID int主键不为null，NAME varchar(20)，PID int，Price Money);
插入TreeTable值(1，’100’，0,3000);
插入TreeTable值(2，’101'，1,3000);
插入TreeTable值(3，’102'，1,3000);
插入TreeTable值(4，’103'，1,3000);
插入TreeTable值(5，’104'，2,3000);
插入TreeTable值(6，’105'，3,3000);
插入TreeTable值(7，’106'，4,3000);
插入TreeTable值(8，’107'，5,3000);
插入TreeTable值(9，’108'，6,3000);
插入TreeTable值(10，’109'，7,3000);
插入TreeTable值(11，’110’，8,3000);
插入TreeTable值(12，’111'，9,3000);

我希望我的份额百分比应从10％开始增加Tree_Level Wise占价格总和的1％.
输出应为
ID名称PID价格树级别份额(％)金额
1100 0 3000.0000 0 10 3600
2 101 1 3000.0000 1 11 3960
3 102 1 3000.0000 1 11 3960
4 103 1 3000.0000 1 11 3960
5 104 2 3000.0000 2 12 4320
6 105 3 3000.0000 2 12 4320
7 106 4 3000.0000 2 12 4320
8 107 5 3000.0000 3 13 4680
9 108 6 3000.0000 3 13 4680
10 109 7 3000.0000 3 13 4680
11110 8 3000.0000 4 14 5040
12 111 9 3000.0000 4 14 5040
总价:36000.0000
我已经完成了这样的任务...

TreeTable
ID Name PID Price
110003000.00
210113000.00
310213000.00
410313000.00
510423000.00
610533000.00
710643000.00
810753000.00
910863000.00
1010973000.00
1111083000.00
1211193000.00

Create table TreeTable(ID int primary key not null,NAME varchar(20),PID int,Price Money);
Insert into TreeTable Values(1,’100’,0,3000);
Insert into TreeTable Values(2,’101’,1,3000);
Insert into TreeTable Values(3,’102’,1,3000);
Insert into TreeTable Values(4,’103’,1,3000);
Insert into TreeTable Values(5,’104’,2,3000);
Insert into TreeTable Values(6,’105’,3,3000);
Insert into TreeTable Values(7,’106’,4,3000);
Insert into TreeTable Values(8,’107’,5,3000);
Insert into TreeTable Values(9,’108’,6,3000);
Insert into TreeTable Values(10,’109’,7,3000);
Insert into TreeTable Values(11,’110’,8,3000);
Insert into TreeTable Values(12,’111’,9,3000);

I want that My share percentage should increase Tree_Level Wise 1% of total sum of Price starting from 10 %.
Output Should Like
IDNamePIDPrice Tree Level Share(%) Amount
110003000.00000103600
210113000.00001113960
310213000.00001113960
410313000.00001113960
510423000.00002124320
610533000.00002124320
710643000.00002124320
810753000.00003134680
910863000.00003134680
1010973000.00003134680
1111083000.00004145040
1211193000.00004145040
Total Price: 36000.0000
I have done Like This…

WITH Tree_CTE(ID,name, PID,price,Tree_level)
AS
(
    SELECT TreeTable.*, 0 FROM TreeTable WHERE PID =0 
    UNION ALL
    SELECT ChildNode.*, ch.Tree_level+1
	  FROM TreeTable AS ChildNode
    INNER JOIN Tree_CTE ch 
    ON ChildNode.PID = ch.ID
)
SELECT ID,name, PID,price,Tree_level FROM Tree_CTE  order by Tree_level 

顺序 输出
ID名称PID价格Tree_level
1100 0 3000.00 0
2 101 1 3000.00 1
3 102 1 3000.00 1
4 103 1 3000.00 1
7 106 4 3000.00 2
6 105 3 3000.00 2
5 104 2 3000.00 2
8 107 5 3000.00 3
9 108 6 3000.00 3
10 109 7 3000.00 3
12 111 9 3000.00 4
11 110 8 3000.00 4

PLZ帮帮我..

Output
ID name PID price Tree_level
110003000.00 0
210113000.00 1
310213000.00 1
410313000.00 1
710643000.00 2
610533000.00 2
510423000.00 2
810753000.00 3
910863000.00 3
1010973000.00 3
1211193000.00 4
1111083000.00 4

PLZ Help me..

推荐答案

SELECT ID,name, PID,price,tree_level, (tree_level+10) as Share , 
            (((select  sum( price ) from tree_cte) * (tree_level+10))/100) as Amount
FROM Tree_CTE

这篇关于我想要这个总价格(36000)的传销的份额计算.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！