oracle 的树状查询得出各个节点值的效率问题！！急，在线等

[findlyhl]

oracle 的树状查询 效率问题：

一个树状结构，每个节点上都对应有一个value，最底层的子节点value 值是手工填报的，然后根据查询出来的子节点的value一级一级的节点往上相加，从而得到每级节点的value值

现在只有400条数据，但是用循环要算出来执行完就需要1分钟多。效率比较低，大家有没有效率高点的方法

1
-1.1
-1.1.1
-1.2
-1.2.1
-1.2.1.1
-1.2.2
-1.2.3
-1.3

....

[suncrafted]

帮顶

[lee24]

如果是oracle 10g的话，可以看下 connect by 的用法

[mumu_java]

放到存储过程中去做.其实是统计每层(除最底层)各个节点的汇总值.效率应该可以.

[findlyhl]

我是这样写的：用了两个function

--得到各级汇总值
FUNCTION GET_ThisFactValue(pConDtlStruID In number,pFacthBathID In Number) RETURN NUMBER
is
vThisFactValue NUMBER(26,2);
vConDtlStruID number;
CURSOR c_2 IS SELECT con_dtl_stru_id
FROM c2cm_con_dtl_stru
WHERE upper_con_dtl_stru_id = pConDtlStruID ;
BEGIN
/* select sum(e.STD_VALUE) into vThisFactValue from c2cm_eng_fact_v e
where e.con_dtl_stru_id=pConDtlStruID
and e.eng_fact_batch_id =pFacthBathID ;*/
vThisFactValue := nvl(GET_ThisFactValue2(pConDtlStruID,pFacthBathID),0);
open c_2;
fetch c_2 into vConDtlStruId;
while c_2%found loop

vThisFactValue :=vThisFactValue + nvl(GET_ThisFactValue(vConDtlStruId,pFacthBathID),0);
fetch c_2 into vConDtlStruId;
end loop;

RETURN vThisFactValue;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;


--获取本级完成值
FUNCTION GET_ThisFactValue2(pConDtlStruID In number,pFacthBathID In Number) RETURN NUMBER
is
vThisFactValue number;
begin
select sum(e.STD_VALUE) into vThisFactValue from c2cm_eng_fact e
where C2CM_ENG_P.GET_CON_BILL_MESS(e.CON_DTL_TO_ID,'CON_DTL','N')=pConDtlStruID
and e.eng_fact_batch_id =pFacthBathID ;
return vThisFactValue;
end;

[Megan2008]

oracle里面不是自带了一个层次查询的语句吗，用那个再加上sql一起步就可以实现了吗

[ehuman]

这个对于你有帮助，数据做的不好，看你能不能理解了。
不明白再说，希望对你有帮助。

select *
  from (select level as lv,
               rpad(' ', level * 2, ' ') ||
               ltrim(sys_connect_by_path(SUB_TABLE.rn, '-'), '-') as path,
               connect_by_isLeaf AS isLeaf
          from (
                
                select rn, lag(rn) over(order by rn) next
                  from (select 'A' || rownum rn from dual connect by rownum < 5)
                union all
                select rn, lag(rn, 1, 'A1') over(order by rn) next
                  from (select 'A1' || rownum rn
                          from dual
                        connect by rownum < 5)
                
                union all
                select rn, lag(rn) over(order by rn) next
                  from (select 'B' || rownum rn
                          from dual
                        connect by rownum < 10)
                union all
                select rn, lag(rn) over(order by rn) next
                  from (select 'C' || rownum rn from dual connect by rownum < 8)) sub_table
        
         start with sub_table.next is null
        connect by prior sub_table.rn = sub_table.next) TABLEA
;