17,086
社区成员
发帖
与我相关
我的任务
分享
select t2.m_name,
sum(decode(rn,1,price,0)) c1,
sum(decode(rn,2,price,0)) c2,
sum(decode(rn,3,price,0)) c3
from
(
select m_code,
price_date,
price,
row_number() over(partition by m_code order by price_date desc) rn
from tb_m_price
) t1,
tb_material t2
where t1.m_code=t2.m_code
group by t2.m_name
m_code c1 c2 c3
----------------------------------------
1 物料1 110 107 105
2 物料2 310 337 205
create table tb1(
v_id number,
v_wl varchar2(10),
v_data1 date ,
v_price1 number,
v_data2 date,
v_price2 number,
v_data3 date,
v_price3 number
);
create or replace procedure pro_test
is
v_id number;
v_wl varchar2(10);
v_data1 date;--最近时间1
v_price1 number;--最近价格1
v_data2 date;
v_price2 number;
v_data3 date;
v_price3 number;
i number:=1;
cursor cur is select a.m_code from tb_material a,tb_m_price b where a.m_code=b.m_code group by a.m_code;
begin
open cur;
loop
fetch cur into v_id;
exit when cur% NOTFOUND;
dbms_output.put_line(v_id);
<<jloop>>
for rect in (select a.m_code,a.m_name,b.price_date,b.price from tb_material a,tb_m_price b where a.m_code=b.m_code order by b.price_date desc) loop
dbms_output.put_line(rect.m_code||'aaaa '||rect.m_name ||'i'||i);
if v_id=rect.m_code then
v_wl:=rect.m_name;
if i=1 then
v_data1:=rect.price_date;
v_price1:=rect.price;
end if;
if i=2 then
v_data2:=rect.price_date;
v_price2:=rect.price;
end if;
if i=3 then
v_data3:=rect.price_date;
v_price3:=rect.price;
end if;
i:=i+1;
end if;
EXIT jloop when i = 4;
end loop jloop;
dbms_output.put_line(v_id || v_wl ||v_data1||v_price1||v_data2||v_price2||v_data3||v_price3);
insert into tb1 values(v_id , v_wl ,v_data1,v_price1,v_data2,v_price2,v_data3,v_price3);
v_data1:=null;
v_price1:=null;
v_data2:=null;
v_price2:=null;
v_data3:=null;
v_price3:=null;
i :=1;
end loop;
close cur;
end;
SQL> select * from tb1;
V_ID V_WL V_DATA1 V_PRICE1 V_DATA2 V_PRICE2 V_DATA3 V_PRICE3
---- ------ -------- --------- --------- ------- ----------- --------
1 物料1 2012-4-6 110 2012-3-5 107 2012-2-4 105
2 物料2 2012-4-6 310 2012-3-5 337 2012-2-4 205