create or replace type tj_type as object(id number,c number)--返回C列计算结果表类型
/
create or replace type tj_tab_type as table of tj_type--返回C列的数据集合
/
create or replace function tj_fun return tj_tab_type --返回C列值的函数
is
l_count number;
l_sum number:=0;
abc_tab tj_tab_type:=tj_tab_type();
begin
select count(*) into l_count from abc;
for i in 1..l_count
loop
select sum(s.c) into l_sum from (select id,c from abc where id=1
union all
select m.id,n.c from abc m,(select id,a*b c from abc) n where m.id=n.id+1
) s where s.id<=i;
--dbms_output.put_line(l_sum); --中间过程调试用
abc_tab.extend;
abc_tab(abc_tab.last):=tj_type(i,l_sum);
end loop;
return abc_tab;
end;
select m.id,m.a,m.b,n.c from (select rownum rn,abc.* from abc) m,table(tj_fun) n where m.id=n.id;
这个例子嵌套了好多子查询,你看看吧!!!
select s.* from
(select * from abc where id=1
union all
select a.id,a.a,a.b,b.a*b.b+b.c c from abc a,
(select id,a,b,c from abc where id=1
union all
select m.id,m.a,m.b,n.d c from abc m,(select id,a*b d from abc) n where m.id=n.id+1) b
where a.id=b.id+1) s,
(select rownum rn,abc.* from abc) t
where s.id=t.id
order by rn;
正解啊[Quote=引用 5 楼 tuies 的回复:]
with t_test as
(
select 1 id, 12 a, 19 b, 0 c from dual union all
select 2, 15 , 17 , 0 from dual union all
select 5, 11 , 32 , 0 from dual union all
select 6, 18 , 79 , 0 from dual union all……
[/Quote]
[Quote=引用 5 楼 tuies 的回复:]
select id,a,b,c,nvl(sum((a*b)) over( order by id rows between unbounded preceding and 1 preceding),0)
from t_test [/Quote]
学习...
[Quote=引用 5 楼 tuies 的回复:]
with t_test as
(
select 1 id, 12 a, 19 b, 0 c from dual union all
select 2, 15 , 17 , 0 from dual union all
select 5, 11 , 32 , 0 from dual union all
select 6, 18 , 79 , 0 from dual union all……
[/Quote]
太神奇了,好好学学分析函数……
with t_test as
(
select 1 id, 12 a, 19 b, 0 c from dual union all
select 2, 15 , 17 , 0 from dual union all
select 5, 11 , 32 , 0 from dual union all
select 6, 18 , 79 , ……
[/Quote]
可以
[Quote=引用 5 楼 tuies 的回复:]
with t_test as
(
select 1 id, 12 a, 19 b, 0 c from dual union all
select 2, 15 , 17 , 0 from dual union all
select 5, 11 , 32 , 0 from dual union all
select 6, 18 , 79 , 0 from dual union all……
[/Quote]
with t_test as
(
select 1 id, 12 a, 19 b, 0 c from dual union all
select 2, 15 , 17 , 0 from dual union all
select 5, 11 , 32 , 0 from dual union all
select 6, 18 , 79 , 0 from dual union all
select 3, 23 , 342, 0 from dual union all
select 4, 134, 545, 0 from dual
)
select id,a,b,c,nvl(sum((a*b)) over( order by id rows between unbounded preceding and 1 preceding),0)
from t_test