select count(*) from
(select id from tbname start with id='A'
connect by prior id=pid
) t
where t.id='G';
(select id from tbname start with id='A'
connect by prior id=pid
) t
where t.id='G';
解决方案 »
- OCIObjectFree 释放不了OCIObjectNew申请的对象,内存没减少
- 请教 关于Oracle存储过程的问题
- 求解:一个求和的 SQL
- C++连接Oracle,提示找不到某个库
- oracle 客户端连接时通时不通 在线求救
- 急!!pb9与oracle9.0.1连接问题
- 用C#+ORACLE和JAVA+ORACLE开发那个好些??
- 一条sql语句单独能执行在程储过程中却不行,哪位帮我看看
- oracle第三方工具,名字叫TOAD的工具在哪里有免费下载的?
- Oracle的驱动程序是不是有问题?
- how to create pfile from spfile???
- 各位oracle同道一定要捧场帮小弟一把啊(建议者有分)
A有 2*5 个E
需要自定义聚组函数(Oracle9i以上),或是使用PLSQL代码实现.
15:36:17 SQL> select * from ttree start with id='B' or id='C' connect by prior id=pid;ID PID PRICE
---------- ---------- ----------
B A 2
D B 3
G D 2
E B 5
C A 1
F C 2
G C 5已选择7行。已用时间: 00: 00: 00.31
15:37:19 SQL> select * from ttree start with id='B' or id='C' connect by prior id=pid
15:37:39 2 intersect
15:37:45 3 select * from ttree start with id='G' connect by prior pid=id;ID PID PRICE
---------- ---------- ----------
B A 2
C A 1
D B 3
G C 5
G D 2已用时间: 00: 00: 00.32
15:38:07 SQL>
但这里面包含两条主线,A->B->D->G A->C->G
用sql语句很难将其分开,而且还要计算乘积
- - ----------
B A 2
D B 3
E B 5
G D 2
C A 1
F C 2
G C 5已选择7行。已用时间: 00: 00: 00.32
15:54:05 SQL> select sum(a.count1*b.count1) from
15:54:08 2 (select id,pid,count1,level leva from t1 start with id='G' connect
by prior pid=id order by level) a,
15:54:08 3 (select id,pid,count1,level levb from t1 start with id='G' connect
by prior pid=id order by level) b
15:54:08 4 where a.id=b.pid and a.leva=b.levb+1;SUM(A.COUNT1*B.COUNT1)
----------------------
17已用时间: 00: 00: 00.00
- - ----------
B A 2
D B 3
E B 5
G D 2
C A 1
F C 2
G C 5已选择7行。已用时间: 00: 00: 00.15
16:27:03 SQL> select nvl(sum(a.count1*b.count1),0) from
16:27:13 2 (select id,pid,count1,level leva from t1 where (id,pid,count1) in
(select id,pid,count1 from t1 start with id='G'
16:27:13 3 connect by prior pid=id) start with pid='A'
16:27:13 4 connect by prior id=pid) a,
16:27:13 5 (select id,pid,count1,level levb from t1 where (id,pid,count1) in
(select id,pid,count1 from t1 start with id='G'
16:27:13 6 connect by prior pid=id) start with pid='A'
16:27:13 7 connect by prior id=pid) b
16:27:13 8 where a.pid=b.id and a.leva=b.levb+1;NVL(SUM(A.COUNT1*B.COUNT1),0)
-----------------------------
17已用时间: 00: 00: 00.00
16:27:13 SQL> select nvl(sum(a.count1*b.count1),0) from
16:27:26 2 (select id,pid,count1,level leva from t1 where (id,pid,count1) in
(select id,pid,count1 from t1 start with id='G'
16:27:26 3 connect by prior pid=id) start with pid='E'
16:27:26 4 connect by prior id=pid) a,
16:27:26 5 (select id,pid,count1,level levb from t1 where (id,pid,count1) in
(select id,pid,count1 from t1 start with id='G'
16:27:26 6 connect by prior pid=id) start with pid='E'
16:27:26 7 connect by prior id=pid) b
16:27:26 8 where a.pid=b.id and a.leva=b.levb+1;NVL(SUM(A.COUNT1*B.COUNT1),0)
-----------------------------
0已用时间: 00: 00: 00.16可以修改sql中id和pid的值
ln_cnt number := 0;
ln_tmp number := 0;
begin
for c in (select level, cnt
from t
start with id = 'G'
connect by prior pid = id) loop
if c.level = 1 then
ln_cnt := ln_cnt + ln_tmp;
ln_tmp := c.cnt;
else
ln_tmp := ln_tmp * c.cnt;
end if;
end loop;
dbms_output.put_line(ln_cnt);
end;
/