分析某单位、某问题、某时间段内各年度问题发生的个数

Question

为实现该问题我建立了三张表如下：
    表一 ES_AUDITRECORDS  审计记录表RECORDID 记录编号
AUDITTYPEID 审计类型编码
COMPANYID 单位编号
AUDITTIME 审计时间
PROBLEMTYPEID 问题类型编号   表二 ES_PROBLEMSTYPE  问题类型表
PROBLEMTYPEID 问题类型编号
PROBLEMTYPENAME 问题类型名称  
   表三 ES_COMPANYINFO   单位信息表
COMPANYID 单位编号
COMPANYNAME 单位名称分析某单位、某问题、某时间段内各年度问题发生的个数  现在假定单位名称给的是：中国公司
                                                   时间段  2000-2009我想要得到以下表：AUDITTIME 财务决算滞后 费用列支范围混淆 结转固定资产滞后 修改资料不齐全 
2000 1 1 3 5 
2001 2 3 4 4 
2002 3 4 4 4 
2003 4 5 4 3 .
.
.
2009 1 3 4 3
SQL语句怎么写？
注：财务决算滞后 费用列支范围混淆 结转固定资产滞后 修改资料不齐全    等是问题种类  
表中的数据为每年该问题发生的个数。 

Answer 1

你试试这个可以不？
create or replace package RefCursor is 　　
 type t_RefCursor is ref cursor; 　
end RefCursor;CREATE OR REPLACE procedure test(time1 in varchar2,time2 in varchar2) is
v_cur RefCursor.t_Refcursor; 
cr RefCursor.t_Refcursor; 
v_bh varchar2(10);  --这个与表中“问题类型编号”字段类型一致
v_mc varchar2(10);  --这个与表中“问题类型名称”字段类型一致
v_sql varchar2(4000); 
v_sql1 varchar2(1000):='';
begin 
v_sql :='create or replace view aa as '
v_sql1:= 'select null ';
open cr for 'select distinct PROBLEMTYPEID from ES_AUDITRECORDS where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'1111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||'''';
fetch cr into v_bh; 
select PROBLEMTYPENAME into v_mc from ES_PROBLEMSTYPE where PROBLEMTYPEID=v_bh;
while cr %found loop 
v_sql1:=v_sql||' , sum(case PROBLEMTYPEID when ''' || v_bh || ''' then 1 else 0 end) as "' || v_mc||'"';  
fetch cr into v_aihao; 
end loop; 
v_sql:=v_sql || ' from ES_AUDITRECORDS  where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'11111111111111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||'''';
v_sql:=v_sql ||'('||v_sql1||')';
execute immediate v_sql;
end; 
/select * from aa;

Answer 2

调用过程exec test('2000','2009');创建视图，两个参数为起始和截止年度，如果都为空则创建所有年份的视图。
刚看到过程有个问题，我先改改

Answer 3

CREATE OR REPLACE procedure test(time1 in varchar2,time2 in varchar2) is
v_cur RefCursor.t_Refcursor; 
cr RefCursor.t_Refcursor; 
v_bh varchar2(10);  --这个与表中“问题类型编号”字段类型一致
v_mc varchar2(10);  --这个与表中“问题类型名称”字段类型一致
v_sql varchar2(4000); 
v_sql1 varchar2(1000):='';
begin 
v_sql :='create or replace view aa as '
v_sql1:= 'select to_char(AUDITTIME,''yyyy'') as 年份 ';
open cr for 'select distinct PROBLEMTYPEID from ES_AUDITRECORDS where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'1111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||'''';
fetch cr into v_bh; 
select PROBLEMTYPENAME into v_mc from ES_PROBLEMSTYPE where PROBLEMTYPEID=v_bh;
while cr %found loop 
v_sql1:=v_sql||' , sum(case PROBLEMTYPEID when ''' || v_bh || ''' then 1 else 0 end) as "' || v_mc||'"';  
fetch cr into v_bh; 
select PROBLEMTYPENAME into v_mc from ES_PROBLEMSTYPE where PROBLEMTYPEID=v_bh;
end loop; 
v_sql:=v_sql || ' from ES_AUDITRECORDS  where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'11111111111111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||''''||' group by 年份';
v_sql:=v_sql ||'('||v_sql1||')';
execute immediate v_sql;
end; 
/

Answer 4

补充下：
表一：
RECORDID    NUMBER(20)
AUDITTIME     DATE
表二：
    PROBLEMTYPEID     NUMBER(20)
    PROBLEMTYPENAME    VARCHAR2(100)
表三：
COMPANYID    VARCHAR2(100)
COMPANYNAME   VARCHAR2(500) 其他没注明的都是VARCHAR2

Answer 5

试试行不行，写的比较混乱可能还有问题第一步
create or replace package RefCursor is 　　
 type t_RefCursor is ref cursor; 　
end RefCursor;
第二步
CREATE OR REPLACE procedure test(time1 in varchar2,time2 in varchar2) is
v_cur RefCursor.t_Refcursor; 
cr RefCursor.t_Refcursor; 
v_bh number(20);  --这个与表中“问题类型编号”字段类型一致
v_mc varchar2(100);  --这个与表中“问题类型名称”字段类型一致
v_sql varchar2(4000); 
v_sql1 varchar2(1000):='';
begin 
v_sql :='create or replace view aa as '
v_sql1:= 'select to_char(AUDITTIME,''yyyy'') as 年份 ';
open cr for 'select distinct PROBLEMTYPEID from ES_AUDITRECORDS where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'1111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||'''';
fetch cr into v_bh; 
select PROBLEMTYPENAME into v_mc from ES_PROBLEMSTYPE where PROBLEMTYPEID=v_bh;
while cr %found loop 
v_sql1:=v_sql||' , sum(case PROBLEMTYPEID when ''' || v_bh || ''' then 1 else 0 end) as "' || v_mc||'"';  
fetch cr into v_bh; 
select PROBLEMTYPENAME into v_mc from ES_PROBLEMSTYPE where PROBLEMTYPEID=v_bh;
end loop; 
v_sql:=v_sql || ' from ES_AUDITRECORDS  where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'11111111111111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||''''||' group by 年份';
v_sql:=v_sql ||'('||v_sql1||')';
execute immediate v_sql;
end; 
/
第三步
--调用过程,如
exec test('2000','2009');
--创建视图，两个参数为起始和截止年度，如果都为空则创建所有年份的视图。
第四步
select * from aa;

Answer 6

在oracle中 视图可以分步执行吗？

Answer 7

我的意思是说，你写的这些sql语句在plsql中怎么运行？

Answer 8

plsql没有用过，应该直接按步骤粘进去，每一步执行一次就可以吧，第三步需要根据你的需求来调整两个参数

Answer 9

我在plsql执行窗口运行该代码  半天没反应，我想关掉plsql窗口却提示查询正在进行！

Answer 10

v_sql:=v_sql || ' from ES_AUDITRECORDS  where to_char(AUDITTIME,''yyyy'')>='||''2000''||nvl(time1,'11111111111111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''2009''||nvl(time2,'9999')||''''||' group by 年份';
我想选择2000年到2009年的   该语句改成？

Answer 11

过程不用改，如果想选择2000年到2009年
第三步直接执行exec test('2000','2009');
其它的不变

Answer 12

exec test('2000','2009');
执行该步时提示  无效的sql语句  是什么原因？

Answer 13

执行下面的重建过程，在调用一次看看
CREATE OR REPLACE procedure test(time1 in varchar2,time2 in varchar2) is
v_cur RefCursor.t_Refcursor; 
cr RefCursor.t_Refcursor; 
v_bh number(20);  --这个与表中“问题类型编号”字段类型一致
v_mc varchar2(100);  --这个与表中“问题类型名称”字段类型一致
v_sql varchar2(4000); 
v_sql1 varchar2(1000):='';
begin 
v_sql :='create or replace view aa as '
v_sql1:= 'select to_char(AUDITTIME,''yyyy'') as 年份 ';
open cr for 'select distinct PROBLEMTYPEID from ES_AUDITRECORDS where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'1111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||'''';
fetch cr into v_bh; 
select PROBLEMTYPENAME into v_mc from ES_PROBLEMSTYPE where PROBLEMTYPEID=v_bh;
while cr %found loop 
v_sql1:=v_sql||' , sum(case PROBLEMTYPEID when ''' || v_bh || ''' then 1 else 0 end) as "' || v_mc||'"';  
fetch cr into v_bh; 
select PROBLEMTYPENAME into v_mc from ES_PROBLEMSTYPE where PROBLEMTYPEID=v_bh;
end loop; 
v_sql1:=v_sql1 || ' from ES_AUDITRECORDS  where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'11111111111111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||''''||' group by 年份';
v_sql:=v_sql ||'('||v_sql1||')';
execute immediate v_sql;
end; 
/

Answer 14

我重新执行了，但第三部还是报无效的sql语句！估计得等到明天挨训了！

Answer 15

再重建，在试
CREATE OR REPLACE procedure test(time1 in varchar2,time2 in varchar2) is
cr RefCursor.t_Refcursor; 
v_bh number(20);  --这个与表中“问题类型编号”字段类型一致
v_mc varchar2(100);  --这个与表中“问题类型名称”字段类型一致
v_sql varchar2(4000); 
v_sql1 varchar2(1000):='';
begin 
v_sql :='create or replace view aa as '
v_sql1:= 'select to_char(AUDITTIME,''yyyy'') as 年份 ';
open cr for 'select distinct PROBLEMTYPEID from ES_AUDITRECORDS where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'1111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||'''';
fetch cr into v_bh; 
select PROBLEMTYPENAME into v_mc from ES_PROBLEMSTYPE where PROBLEMTYPEID=v_bh;
while cr %found loop 
v_sql1:=v_sql||' , sum(case PROBLEMTYPEID when ''' || v_bh || ''' then 1 else 0 end) as "' || v_mc||'"';  
fetch cr into v_bh; 
select PROBLEMTYPENAME into v_mc from ES_PROBLEMSTYPE where PROBLEMTYPEID=v_bh;
end loop; 
v_sql1:=v_sql1 || ' from ES_AUDITRECORDS  where to_char(AUDITTIME,''yyyy'')>='||''''||nvl(time1,'1111')||''''||' and to_char(AUDITTIME,''yyyy'')<='||''''||nvl(time2,'9999')||''''||' group by to_char(AUDITTIME,''yyyy'')';
v_sql:=v_sql ||'('||v_sql1||')';
execute immediate v_sql;
end; 
/

Answer 16

即使刚才解决了也没用了因为第二张表还有一个字段
PARENTTYPEID   上级问题类型编号
我的问题是分为大类  子类  描述   三层的  
我现在要选取的结果是   问题大类了！
更麻烦了！问题大类也是动态的  从1开始编号的  
问题大类有   工程管理  成本费用  等最后选择的结果应该是：
AUDITTIME 工程管理 成本费用 
2000        1       1 
2001        2       3 
2002        3       4 
2003        4       5 
. 
. 
. 
2009        3       3

Answer 17

第一个问题：
SELECT AUDITTIME, 
       p1 财务决算滞后, 
       p2 费用列支范围混淆,
       p3 结转固定资产滞后,
       p4 修改资料不齐全  
FROM (
SELECT  AUDITTIME,
        SUM(decode(t.PROBLEMTYPEID,'1',1,0)) p1,
        SUM(decode(t.PROBLEMTYPEID,'2',1,0)) p2,
        SUM(decode(t.PROBLEMTYPEID,'3',1,0)) p3,
        SUM(decode(t.PROBLEMTYPEID,'4',1,0)) p4
FROM svc_guest_sended t
GROUP BY p_year)
第二个问题：
如果是oracle 10G 则可用它的一个新特性：CONNECT_BY_ROOT 作用就是使结果不是当前的节点ID，而满足查询条件下的根节点的ID
select rootindex, count('X') from
    (select CONNECT_BY_ROOT dirindex as rootindex 
    from t_tonedirlib
    start with fatherindex = 0
    connect by  fatherindex =  prior dirindex) a
group by a.rootindex
ROOTINDEX COUNT('X')

Answer 18

既然是统计，可以把所有的问题类别都统计出来的
如果子类别统计出来了，那父类也是可以汇总出来的，不过要跟类别表关联起来统计如果还要加上group by COMPANYID 

Answer 19

其实这个方法，我觉得挺好。用9I的decode，让我对decode有更深的体会！