题目:假如输入变量是cur_date varchar2
统计以下变量:
今天的产量 cur_date
昨天的产量 to_char(to_date(cur_date,'yyyymmdd')-1,'yyyymmdd')
本月的产量 substr(cur_date,1,6)
本年的产量 substr(cur_date,1,4)
去年同期的产量 to_char(add_months(to_date(cur_date,'yyyymmdd'),-12),'yyyymmdd')
1)用基本SQL来实现
用FOR循环游标来实现
考核的知识点:FOR循环,游标,时间值函数,转换函数,DECODE/CASE用法
这个题还需各位高手。
参考答案如下:
select *
from factory
select to_char(to_date('20080602','yyyymmdd') -1,'yyyymmdd')
from dual
select to_char(add_months(to_date(f.cur_date,'yyyymmdd'),-12),'yyyymmdd')
from dual
create table temp
as
select cur_date,(select
sum(case when cur_date=f.cur_date then cur_perout
else 0
end)
from factory) a,
(select
sum(case when cur_date=to_char(to_date(f.cur_date,'yyyymmdd') -1,'yyyymmdd')
then cur_perout else 0
end)
from factory) b ,
(select
sum(case when substr(cur_date,1,6)=substr(f.cur_date,1,6) then cur_perout
else 0
end)
from factory) c,
(select
sum(case when substr(cur_date,1,4)=substr(f.cur_date,1,4) then cur_perout
else 0
end)
from factory) d,
(select
sum(case when cur_date=to_char(add_months(to_date(f.cur_date,'yyyymmdd'),-12),'yyyymmdd')
then cur_perout else 0
end)
from factory) e
from factory f
where rownum <1
-- where cur_date='20080602';
begin
for temstr in (select * from factory) loop
insert into temp
select temstr.cur_date,--游标(不带字段/常量)
sum(case when cur_date=temstr.cur_date then cur_perout
else 0
end),
sum(case when cur_date=to_char(to_date(temstr.cur_date,'yyyymmdd') -1,'yyyymmdd')
then cur_perout
else 0
end),
sum(case when substr(cur_date,1,6)=substr(temstr.cur_date,1,6) then cur_perout
else 0
end),
sum(case when substr(cur_date,1,4)=substr(temstr.cur_date,1,4) then cur_perout
else 0
end),
sum(case when cur_date=to_char(add_months(to_date(temstr.cur_date,'yyyymmdd'),-12),'yyyymmdd') then cur_perout
else 0
end)
from factory;
end loop;
end;
select * from temp