有三个表 汇总月表Total a ,业务月表Business b ,状态月表Status c ,对象表Object d,前三个表都有时间戳做结尾,统计当月的总数我这样写:
select a.datadate,sum(a.total) total,sum(b.businesstotal) businesstotal,c.status
from Total200901 a,Business200901 b, Status200901 b,Object d
where d.objectid = a.objectid
and d.objectid= b.objectid
and d.objectid = c.objectid
and a.datadate>=to_date('20090101','yyyy-mm-dd')
and a.datadate<=to_date('20090131','yyyy-mm-dd')
group by a.datadate,c.status但统计跨月的数据时,表名是动态变化的,比如我查询时间段为20090101至20090301时,要union200901,02,03三个月表,不知这个存储过程改怎么写,传入参数有四个,objectid,month(格式YYYYMM),startdate(格式YYYYMMDD),enddate(格式YYYYMMDD)
select a.datadate,sum(a.total) total,sum(b.businesstotal) businesstotal,c.status
from Total200901 a,Business200901 b, Status200901 b,Object d
where d.objectid = a.objectid
and d.objectid= b.objectid
and d.objectid = c.objectid
and a.datadate>=to_date('20090101','yyyy-mm-dd')
and a.datadate<=to_date('20090131','yyyy-mm-dd')
group by a.datadate,c.status但统计跨月的数据时,表名是动态变化的,比如我查询时间段为20090101至20090301时,要union200901,02,03三个月表,不知这个存储过程改怎么写,传入参数有四个,objectid,month(格式YYYYMM),startdate(格式YYYYMMDD),enddate(格式YYYYMMDD)
解决方案 »
- 用触发器实现两个表中数据的更新!
- Oracle中包与函数的区别???
- oracle 字段截取
- OracleServiceORCL oracle的这个服务被我误删了 有办法恢复吗 9i的..
- 第一次提问就赶上黄金周末,sql语句查询无响应,求助
- 求SQL,查询数据库表其中3-6条的数据记录,不作排序。猛谢!
- 谁有现成的oracle9i安装说明(切的图片那种),偶是拿给给客户看这安装的。
- 如何将sqlserver中的数据导入oracle中,急!!!
- 在从access倒入oracle9i的时候,出现“插入的值对于列过大“的错误,怎么办?
- 大神们求一sql
- sql 累加字段的问题 50分!
- 新手请教:如何知道某条记录已被锁住了?
declare
i integer;
d date;
d_start date;
d_end date;
BEGIN
d_start:=to_date('20080101','yyyymm');
d_end:=to_date('20081201','yyyymm');
d:=d_start;
for i in 0..d_end-d_start loop
dbms_output.put_line(d+i);
end loop;
END;
传入的4个参数中后两个我明白
但objectid,month是干什么用的
objectid是对象ID,每个表都有的,用来表连接,month(格式YYYYMM)是月表Total,Business, Status的日期值,是动态的,例如month为200901,则表名是Total200901 ,Business200901 , Status200901统计当月的总数用传入变量来写是
select a.datadate,sum(a.total) total,sum(b.businesstotal) businesstotal,c.status
from Total$month$ a,Business$month$ b, Status$month$ b,Object d
where d.objectid = $objectId$
and d.objectid = a.objectid
and d.objectid= b.objectid
and d.objectid = c.objectid
and a.datadate>=to_date($startdate$,'yyyy-mm-dd')
and a.datadate <=to_date($startdate$,'yyyy-mm-dd')
group by a.datadate,c.status
要根据startdate和enddate来取,将几个查询结果union起来。对吧
create or replace procedure proc(objectid in number,startdate in varchar2,enddate in varchar2)
as
sqlstr varchar2(4000);
begin
for i in 0..to_number(substr(enddate,6,2))-to_number(substr(startdate,6,2)) loop
if i>0 then sqlstr:=sqlstr||' union all
' ; end if;
sqlstr:=sqlstr||'select a.datadate,sum(a.total)total,sum(b.businesstotal)businesstotal,c.status
from total'||substr(startdate,1,4)||to_char(substr(startdate,6,2)+i,'fm00')||' a,business'
||substr(startdate,1,4)||to_char(substr(startdate,6,2)+i,'fm00')||' b,status'
||substr(startdate,1,4)||to_char(substr(startdate,6,2)+i,'fm00')||' c,object d
where d.objectid='||objectid||'
and d.objectid=a.objectid
and d.objectid=b.objectid
and d.objectid=c.objectid
and a.datadate>=to_date('''||startdate||'''),''yyyy-mm-dd'')
and a.datadate<=to_date('''||enddate||'''),''yyyy-mm-dd'')
group by a.datadate,c.status';
end loop;
--可以用dbms_output.put_line(sqlstr);先看看生成的代码是否正确
execute immediate 'create or replace view v_hz as '||sqlstr;
end proc;
执行存储过程生成v_hz这个视图,执行视图查询你要的结果
select * from v_hz;