关于树统计，如何得到多个统计

Question

小弟只会这样写select t.*,(select sum(pid) from tree start with id=t.id connect by prior id=pid and id!=0) pid_sum,(select sum(ordernum) from tree start with id=t.id connect by prior id=pid and id!=0) ordernum_sum from tree t效率是不是很低呢？
有没有好一点的做法？
谢谢了

Answer 1

忘记了，建立数据应该把
alter table TREE
  add constraint TREE_PID foreign key (PID)
  references TREE (ID) on delete cascade;insert into TREE (ID, PID, NAME, ORDERNUM)
values (0, 0, '根', null);对调一下，要不然数据进不去。

Answer 2

可以考虑使用递归存储过程来实现。
--4.1 数据表结构 
--BOM表样例，里面包含较为简单的BOM层次结构 
Create Table Test_Bom ( 
  ItemKey VarChar2(10) Not Null--产品(或材料)主键，关联产品基本档，在此不进行详细描述。原应为数值类型，在此以字符类型表示，以方便查看和说明。 
  ,MaterialKey VarChar2(10) Not Null--半成品(或材料)主键，关联产品基本档，在此不进行详细描述。原应为数值类型，在此以字符类型表示，以方便查看和说明。 
  ,Qty Number(6,2) Not Null--单位用量 
  ,constraint PK_Test_Bom primary key (ItemKey,MaterialKey) 
); 
--4.2 测试数据 
--A由A1和A2构成 
Delete From Test_Bom; 
Insert Into Test_Bom (MaterialKey, ItemKey,Qty) values (’a1’,’a’,1); 
Insert Into Test_Bom (MaterialKey, ItemKey,Qty) values (’a2’,’a’,2); 
--A1由A11和A12构成 
Insert Into Test_Bom (MaterialKey, ItemKey,Qty) values (’a11’,’a1’,3); 
Insert Into Test_Bom (MaterialKey, ItemKey,Qty) values (’a12’,’a1’,4); 
--A11由A111和A112构成 
Insert Into Test_Bom (MaterialKey, ItemKey,Qty) values (’a111’,’a11’,5); 
Insert Into Test_Bom (MaterialKey, ItemKey,Qty) values (’a112’,’a11’,6); 
--A12由A121和A122以及A123构成 
Insert Into Test_Bom (MaterialKey, ItemKey,Qty) values (’a121’,’a12’,7); 
Insert Into Test_Bom (MaterialKey, ItemKey,Qty) values (’a122’,’a12’,8); 
Insert Into Test_Bom (MaterialKey, ItemKey,Qty) values (’a123’,’a12’,9); 
--A2由A21和A22构成 
Insert Into Test_Bom (MaterialKey, ItemKey,Qty) values (’a21’,’a2’,10); 
Insert Into Test_Bom (MaterialKey, ItemKey,Qty) values (’a22’,’a2’,11); 
--A21由A212和A1构成 
Insert Into Test_Bom (MaterialKey, ItemKey,Qty) values (’a212’,’a21’,12); 
Insert Into Test_Bom (MaterialKey, ItemKey,Qty) values (’a1’,’a21’,5); 
Commit; 
--由此看出真正需要购买的原材料是：a111,a112,a121,a122,a123,a22,a212 
--4.3 需求 
--要列出产品A的材料清单，以方便核算成本和请购材料。 
--4.4 实现方法 
--4.4.1 创建临时表，用于存储计算结果 
------------------------------------------------------------------------------------------- 
--为方便查看，本例使用常规数据表，实际应用过程中，应使用临时表，避免用户之间的干扰 
--create  Global Temporary table Test_Bom_MaterialList_TP( 
       MaterialKey VarChar2(10) Not Null 
--   ,Qty Number(10) Not Null 
--) 
--   On Commit PreServe Rows 
--/ 
------------------------------------------------------------------------------------------- 
create  table Test_Bom_MaterialList_TP( 
   MaterialKey VarChar2(10) Not Null 
   ,Qty Number(10) Not Null 
) 
/ --4.4.2 创建程序包，用于计算原材料用量 
CREATE OR REPLACE Procedure SP_ExportMaterial 
      (P_ItemKey In Varchar2,P_Qty Number) 
   is 
      --============================================= 
      --创建存放游标数据的记录类型 
      --============================================= 
      type Material_rec is record( 
         ItemKey      Test_Bom.ItemKey%type, 
         MaterialKey Test_Bom.MaterialKey%type, 
         Qty   Test_Bom.Qty%Type);       --============================================= 
      --创建存放游标记录的记录集对象 
      --============================================= 
      Material_Record Material_Rec;       --============================================= 
      --创建游标，游标的记录集数据类型为Material_Rec 
      --============================================= 
      Type Material_Cur is ref cursor Return Material_Rec;       --============================================= 
      --创建游标对象 
      --============================================= 
      Material_Cursor Material_Cur;       L_IsNextLayer Numeric;--存放是否有下一层物料的变量 
      L_MaterialQty Numeric;--当前物料的单位用量 
   begin 
      --============================================= 
      --打开游标，传入数据 
      --============================================= 
      open Material_CurSor for 
         select ItemKey --主产品KEY 
            ,MaterialKey --物料KEY 
            ,Qty --物料标准用量 
         from Test_Bom 
         Where ItemKey=P_ItemKey; 
      --============================================= 
      --将数据传入BOM_MaterialList_TP临时数据表 
      --============================================= 
      --将游标数据推入Material_Record记录集对象\ 
      Fetch Material_CurSor Into Material_Record; 
      While Material_Cursor%Found --判断游标是否为空 
      Loop 
         --判断当前物料编号是否有下一层物料 
         Select Count(0) Into L_IsNextLayer 
            From Test_Bom 
            Where ItemKey=Material_Record.MaterialKey; 
         If Not L_IsNextLayer=0 Then 
            --如果有下层物料，则执行再调用SP_ExportMaterial<自身程序，递归点> 
            SP_ExportMaterial 
               (Material_Record.MaterialKey 
                  ,P_Qty*Material_Record.Qty); 
         Else 
            --将当前游标数据传入Test_Bom_MaterialList_TP临时数据表 
            Insert Into Test_Bom_MaterialList_TP 
               (MaterialKey,Qty) 
               values(Material_Record.MaterialKey 
                  ,P_Qty*Material_Record.Qty); 
            --将游标数据推入Material_Record记录集对象 
         End if; 
         Fetch Material_CurSor Into Material_Record; 
      End Loop; 
      Commit; 
   end SP_ExportMaterial; 
     
--4.5 测试 
Delete From Test_Bom_MaterialList_TP; --删除临时表数据 
SP_ExportMaterial(’a1’,1); --执行计算程序 
Select * from Test_Bom_MaterialList_TP;

Answer 3

谢谢flamingo100(lxj) 兄弟。不过我不是想做递归查询，而是统计。业务实际比如说树作为文章类别，文章表关联到此类别树。我要对文章表的几个字段作sum或者avg、count等统计，更进一步讲最后还要求对树确定深度的所有节点作这个统计，而这些节点有可能包括若干不同深度子节点；或者对不同深度的节点作统计比较。。我是打死也写不出来了，高手们帮帮忙啊。http://community.csdn.net/Expert/topic/3450/3450775.xml?temp=.6058618
我又另开一贴，总共200分，不够可以再加

Answer 4

我想问一下
select (some value) value, sum(some sum use condition value) from table
有没有其他Oracle语法
就像上面我写的
select t.*,(select sum(pid) from tree start with id=t.id connect by prior id=pid and id!=0) pid_sum,(select sum(ordernum) from tree start with id=t.id connect by prior id=pid and id!=0) ordernum_sum from tree t
pid_sum和ordernum_sum使用外面查询的t.id作为条件作sum。两个查询是一样的，可是不能写在一起，是不是影响性能了？
应该有其他Oracle写法吧，高手们教教我呀~~~

Answer 5

flamingo100(lxj) 运行测试
Delete From Test_Bom_MaterialList_TP; --删除临时表数据 
SP_ExportMaterial(’a1’,1); --执行计算程序 
Select * from Test_Bom_MaterialList_TP;
在SQL PLUS WORKSHEET中提示 
第 1 行出现错误:
ORA-00911: 无效字符

Answer 6

begin
Delete From Test_Bom_MaterialList_TP;
SP_ExportMaterial('a1',1); --执行计算程序 
end;

Answer 7

try:
select substr(tre,2,instr(tre,'/',1,2)) id,count(*) col1,sum(pid) col2,
sun(orderno) col3 from (
select  id,pid,level,SYS_CONNECT_BY_PATH(id,  '/')||'/' tre  
   from  tree  
 start  with  id  <> 0  
connect  by  prior  id  =  pid) t group by  substr(tre,2,instr(tre,'/',1,2));

Answer 8

flamingo100(lxj) 再问几个问题：1.创建的程序包写好后在那里可以修改？2.如果产品中A11是也是一个原材料（如：电源）、A111和A112只是电源上的粘帖纸，在生产时需要计算A11。请问这样程序包怎样写？3.还有我需要同时计算价格，但价格是在其他的表中的，请问这样程序包怎样写？4.我怎样将其运行程序整合到VB或APS网页程序中！也就是使用者不需要使用SQL PLUS WORKSHEET。谢谢！

Answer 9

1。调试工具有很多，比如SQL navigator，sql plus worksheet也行，但不太好使
2。你是说计算A11用了多少A111和A112?
3。用视图。
4。可以在vb里调用command对象来执行你存储过程

Answer 10

再次谢谢bzszp，结贴，问题解答请看http://community.csdn.net/Expert/topic/3450/3450775.xml?temp=.6058618