本帖最后由 ri6876099 于 2012-08-13 17:19:03 编辑 create or replace procedure JCJGMONTHCOUNT
authid current_user
is
vn_ctn number;
begin
select count(*) into vn_ctn from user_all_tables a where a.table_name like upper('JCJGMONTHTABLE');
if vn_ctn > 0 then
execute immediate 'drop table JCJGMONTHTABLE';
end if;
execute immediate 'create table JCJGMONTHTABLE as
select * from (select qii.inspectinstitutionpid,qii.inspectinstitutionName,qii.jcjgid,
qcr.approveDate,
count(*) as SAMPLEGROUPID,
sum(case when qcr.result in(''A'') then 1 else 0 end) "RESULT_A", --结论
sum(case when qcr.RESULT in(''B'') then 1 else 0 end) "RESULT_B",
sum(case when qcr.RESULT in(''D'') then 1 else 0 end) "RESULT_D",
sum(case when qcr.RESULT in(''C'') then 1 else 0 end) "RESULT_C",
sum(case when qcr.RESULT in(''E'') then 1 else 0 end) "RESULT_E",
sum(case when qcr.processstate in(''N'') then 1 else 0 end) "PROCESSSTATE",--曲线比对异常
sum(case when qcr.experimentationstate in(''N'') then 1 else 0 end) "EXPERIMENTATIONSTATE", --比对结果异常
sum(case when qcr.echosampleid is not null then 1 else 0 end) "ECHOSAMPLEID_TRUE", --复检的样品编号_是
sum(case when qcr.echosampleid is null then 1 else 0 end) "ECHOSAMPLEID_FALSE",--复检的样品编号_否
sum(case when qcr.realtimestate in(''N'') then 1 else 0 end) "REALTIMESTATE",--是否实时传输状态
sum(case when qcr.modifystate in(''Y'') then 1 else 0 end) "MODIFYSTATE", --数据被修改
sum(case when qcr.collectionstate in(''N'') then 1 else 0 end) "COLLECTIONSTATE" --未采集曲线数据
from report qcr
inner join compact jc on qcr.compactpid = jc.compactpid
inner join inspectinstitution qii on qcr.jcjgid = qii.jcjgid
where qcr.approveDate=to_date(''2012-1'',''yyyy-mm'')
group by qii.inspectinstitutionpid,qii.inspectinstitutionName,qii.jcjgid,qcr.approveDate)';
exception
when others then
null;
end JCJGMONTHCOUNT;
where qcr.approveDate=to_date(''2012-1'',''yyyy-mm'')
一月时建临时表 2-12时 添加数据怎么循环
------解决方案--------------------
没明白意思
------解决方案--------------------
where qcr.approveDate=to_date(to_char(trunc(sysdate),'yyyy-mm'),''yyyy-mm'')
------解决方案--------------------
where 条件那里传的是月份 就是1月的时候是就是上面的语句2-12月的时候就是往1月建的表当中添加数据
------解决方案--------------------
到2-12月的时候就不是创建表了 而是 insert into JCJGMONTHTABLE(
select * from (select qii.inspectinstitutionpid,qii.inspectinstitutionName,qii.jcjgid,
qcr.approveDate,
count(*) as SAMPLEGROUPID,