create table tb(子节点 varchar(2), 父接节点 varchar(2), 成本 int , 分配率 float)insert into tb values( 'b' , 'a1', 2 ,null )
insert into tb values( 'b' , 'a2', 8,null)
insert into tb values( 'c1' , 'b', 5,null )
insert into tb values( 'C2' , 'b', 3,null)
insert into tb values( 'C3' , 'b', 2,null)
select a.子节点, a.父接节点, a.成本 ,(a.成本 /cast((select sum(成本) from tb where 子节点=a.父接节点 ) as float)) as 分配率
from tb a
insert into tb values( 'b' , 'a2', 8,null)
insert into tb values( 'c1' , 'b', 5,null )
insert into tb values( 'C2' , 'b', 3,null)
insert into tb values( 'C3' , 'b', 2,null)
select a.子节点, a.父接节点, a.成本 ,(a.成本 /cast((select sum(成本) from tb where 子节点=a.父接节点 ) as float)) as 分配率
from tb a
---- ---- ----------- -----------------------------------------------------
b a1 2 NULL
b a2 8 NULL
c1 b 5 0.5
C2 b 3 0.29999999999999999
C3 b 2 0.20000000000000001(所影响的行数为 5 行)
SET @子节点='b';
WITH TBTCE(子节点,父接节点,成本,LVL)
AS
(SELECT 子节点,父接节点,成本,0
FROM TB
WHERE 子节点=@子节点
UNION ALL
SELECT A.子节点,A.父接节点,A.成本,E.LVL+1
FROM TB A INNER JOIN TBTCE E
ON A.父接节点=E.子节点
)
SELECT *,分配率=0 FROM TBTCE WHERE LVL=0
UNION ALL
select *, 分配率=CAST(成本 AS FLOAT)/(SELECT SUM(成本) FROM TBTCE WHERE LVL=0 ) from (SELECT DISTINCT * FROM TBTCE) TB WHERE LVL>0
/*
结果
子节点 父接节点 成本 LVL 分配率
---- ---- ----------- ----------- ----------------------
b a1 2 0 0
b a2 8 0 0
c1 b 5 1 0.5
C2 b 3 1 0.3
C3 b 2 1 0.2(5 行受影响)
*/