解决方案 »
- 9i中可以执行的日期计算语句,10g中报错。为什么
- c# 连接运行存储过程报这个错 ORA-01036: 非法的变量名/编号
- 向大家请教一个SQL(2)
- 为什么删除纪录后,性能反而下降?
- 求助一个关于ORACLE 的SQL 语句。谢谢各位。
- 请教一个问题 .net连接oracle时本机上不装oracle客户端程序能连吗?如果能又该怎么连?
- 创建数据库时出现invalid entry size的错误信息,该如何?
- 过年关喽!今年你挣了多少?
- 为什么扣我的信誉分???(顺便散分)
- 【数据库基础】个人笔记
- 为什么我的存储过程 都说 是带有编译错误 ,应该怎样解决
- 在oracle中,创建的过程带有编译错误,这是什么原因
from
(select col2,
substr(sys_connect_by_path(col3,','),2) aa
from test1
where CONNECT_BY_ISLEAF=1
start with col1='a'
connect by col1= prior col2)
想了办法'1'||sys_connect_by_path(col3,',')||',' into :string 后分解字符串进行乘积
with tbl as
(
select 'a' as col1, 'b1' as col2, 1 as col3 from dual
union all
select 'a' as col1, 'b2' as col2, 0.5 as col3 from dual
union all
select 'b1' as col1, 'c1' as col2, 0.7 as col3 from dual
union all
select 'b2' as col1, 'c2' as col2, 2 as col3 from dual
)
select col2,
dbms_aw.eval_number(1 || SYS_CONNECT_BY_PATH(trim(to_char(col3,'99999990.99')),'*')) as inum
from tbl t
where connect_by_isleaf = 1
connect by prior col2 = col1
start with col1 = 'a';
COL2 INUM
---- ----------
c1 0.7
c2 1
with temp_table as
(
select 'a' as col1, 'b1' as col2, 1 as col3 from dual
union all
select 'a' as col1, 'b2' as col2, 0.5 as col3 from dual
union all
select 'b1' as col1, 'c1' as col2, 0.7 as col3 from dual
union all
select 'b2' as col1, 'c2' as col2, 2 as col3 from dual
)select t1.col2,(t1.col3*t2.col3)
from temp_table t1,
temp_table t2
where t1.col1 = t2.col2--结果
c1 0.7
c2 1
with tbl as
(
select 'a' as col1, 'b1' as col2, 2 as col3 from dual
union all
select 'a' as col1, 'b2' as col2, 0.5 as col3 from dual
union all
select 'b1' as col1, 'c1' as col2, 0.7 as col3 from dual
union all
select 'b2' as col1, 'c2' as col2, 2 as col3 from dual
union all
select 'c1' as col1, 'd1' as col2, 0.8 as col3 from dual
)
select col2,
dbms_aw.eval_number(1 || SYS_CONNECT_BY_PATH(trim(to_char(col3,'99999990.99')),'*')) as inum
from tbl
where connect_by_isleaf = 1
start with col1 = 'a' connect by prior col2 = col1;
把我的脚本改一下 connect by 的位置就来蒙分?
这样,如果是两级就可以,但子节点一多就不行了,可以往这个方向想想..