关于SQL递归统计问题

[huangjinyin]

oracle库
有表A　父节点ID为0表根节点，注父子节点是无限级的

节点ID　父节点ID　时间S（int）
A　　　　0 60
B　　　　A 40
C　　　　A 20
D　　　　0 40

表B
节点ID　　己用时间
B　　　　　10
C　　　　　5
D　　　　　20
求根节点（A与D）所用时间百分比　
结果如
A　60　25%
D　40　50%　　　　

[黄_瓜]

oracle 建议你到oracle专区问问

[永生天地]

参考递归

SELECT *
FROM A
START WITH 父节点ID　=0
CONNECT BY 父节点ID　 = PRIOR 节点ID;

[lzbbob1985]

结果如
A　60　25%
D　40　50%


A 结果很奇怪 迷茫的路过

[ap_jiang]

A 的25%是怎么出来的 ？

[huangjinyin]

A的25%是，子节点B＋C＝60，B＋C己花15　所以是25%

[wh62592855]

SQL> select * from t1;
 
CID        PID                                           TIME
---------- ---------- ---------------------------------------
A          0                                               60
B          A                                               40
C          A                                               20
D          0                                               40
 
SQL> select * from t2;
 
CID                                         PASSED
---------- ---------------------------------------
B                                               10
C                                                5
D                                               20
 
SQL> 
SQL> select root_name,time,percentage
  2  from
  3  (
  4  select root_name,pid,time,sum(passed) over(partition by root_name)/time as percentage
  5  from
  6  (
  7  select a.cid,a.time,a.pid,a.root_name,b.passed
  8  from
  9  (
 10  SELECT CID, PID, TIME, CONNECT_BY_ROOT(CID) ROOT_NAME
 11  FROM T1
 12  START WITH PID = '0'
 13  CONNECT BY PRIOR CID = PID
 14  ) a left join t2 b
 15  on a.cid = b.cid
 16  )
 17  )
 18  where pid = '0'
 19  ;
 
ROOT_NAME                                     TIME PERCENTAGE
---------- --------------------------------------- ----------
A                                               60       0.25
D                                               40        0.5

[嘟嘟xo]

 
SQL> select * from ls_a;
 
ID         PID                            T
---------- -------------------- -----------
A          0                             60
B          A                             40
C          A                             20
D          0                             40
 
SQL> select * from ls_b;
 
ID                   T
---------- -----------
B                   10
C                    5
D                   20
 
SQL> 
SQL> SELECT R_ID,
  2         SUM(TOTAL) TOTAL,
  3         SUM(USED) USED,
  4         SUM(USED) / SUM(TOTAL) PERCENT
  5    FROM (SELECT CONNECT_BY_ROOT LS_A.ID R_ID,
  6                 LS_A.PID,
  7                 DECODE(LS_A.PID, '0', LS_A.T, 0) TOTAL,
  8                 DECODE(LS_A.PID, '0', DECODE(CONNECT_BY_ISLEAF, 1, LS_B.T, 0), LS_B.T) USED
  9            FROM LS_A, LS_B
 10           WHERE LS_A.ID = LS_B.ID(+)
 11           START WITH LS_A.PID = '0'
 12          CONNECT BY PRIOR LS_A.ID = LS_A.PID)
 13   GROUP BY R_ID;
 
R_ID            TOTAL       USED    PERCENT
---------- ---------- ---------- ----------
D                  40         20        0.5
A                  60         15       0.25

[zhangwonderful]

[Quote=引用 6 楼 wh62592855 的回复:]

SQL code
SQL> select * from t1;

CID PID TIME
---------- ---------- ---------------------------------------
A 0 ……
[/Quote]
支持

[小灰狼W]

select 节点id,时间s,sum(已用时间)/时间s 百分比
from(
select connect_by_root a.节点id 节点id,
connect_by_root a.时间s 时间s,b.已用时间
from 表A a,表B b
where a.节点id=b.节点id(+)
start with a.父节点id='0'
connect by prior a.节点id=a.父节点id)
group by 节点id,时间s