简单统计报表例子（存储过程）

 1 create or replace procedure bb_quxzbmjdqzxfqk_ces(kssj in date,
 2                                          jssj in date,
 3                                          wtsd2 varchar2,
 4                                          tjjg out sys_refcursor) as
 5 
 6 begin
 7    delete from A_TJBB_quxzbmjdqzxfqk;
 8 commit;
 9 insert into A_TJBB_quxzbmjdqzxfqk
10 select jj.iidd,jj.isedit,jj.djjgbh,jj.cfxfjbz,jj.lmbz,jj.xfrs,jj.xfxs from visit_xfj jj,
11  hr_organization n where n.jglb='1200' and  n.region_dm=wtsd2 and n.bh=jj.djjgbh and jj.djsj between kssj and jssj;
12 commit;
13   open tjjg for
14 
15 
16 select l.mc as mc,
17  count(1) as jianci,
18 sum(case when xf.xfxs='100' then 1 else
19  case when xf.xfxs='200' then xf.xfrs else
20  case when xf.xfxs in ('300','303') then 1
21  else 0 end end end) as rci,
22 sum(case when xf.xfxs='100' and xf.lmbz='0'and xf.cfxfjbz='0' then 1 else 0 end)as grlx,
23 sum(case when xf.xfxs='100' and xf.lmbz='1'and xf.cfxfjbz='0' then 1 else 0 end)as lmx,
24 sum(case when xf.xfxs='100' and xf.lmbz='0'and xf.cfxfjbz='1' then 1 else 0 end)as cfgrlx,
25 sum(case when xf.xfxs='100' and xf.lmbz='1'and xf.cfxfjbz='1' then 1 else 0 end)as cflmx,
26 sum(case when xf.xfxs='100' then 1 else 0 end)as xiaojie,
27 sum(case when xf.xfxs='200' and xf.xfrs<=4 and xf.cfxfjbz='0' then 1 else 0 end)as c1lfcjjc,
28 sum(case when xf.xfxs='200' and xf.xfrs<=4 and xf.cfxfjbz='0' then xf.xfrs else 0 end)as c1lfcjrc,
29 sum(case when xf.xfxs='200' and( xf.xfrs>=5 and xf.xfrs<=49) and xf.cfxfjbz='0' then 1 else 0 end)as c5lfcjjc,
30 sum(case when xf.xfxs='200' and( xf.xfrs>=5 and xf.xfrs<=49) and xf.cfxfjbz='0' then xf.xfrs else 0 end)as c5lfcjrc,
31 sum(case when xf.xfxs='200' and( xf.xfrs>=50 and xf.xfrs<=499) and xf.cfxfjbz='0' then 1 else 0 end)as c50lfcjjc,
32 sum(case when xf.xfxs='200' and( xf.xfrs>=50 and xf.xfrs<=499) and xf.cfxfjbz='0' then  xf.xfrs else 0 end)as c50lfcjrc,
33 sum(case when xf.xfxs='200' and xf.xfrs>=500 and xf.cfxfjbz='0' then 1 else 0 end)as c500lfcjjc,
34 sum(case when xf.xfxs='200' and xf.xfrs>=500 and xf.cfxfjbz='0' then  xf.xfrs else 0 end)as c500lfcjrc,
35 sum(case when xf.xfxs='200' and xf.xfrs<=4 and xf.cfxfjbz='1' then 1 else 0 end)as cf1lfcjjc,
36 sum(case when xf.xfxs='200' and xf.xfrs<=4 and xf.cfxfjbz='1' then xf.xfrs else 0 end)as cf1lfcjrc,
37 sum(case when xf.xfxs='200' and( xf.xfrs>=5 and xf.xfrs<=49) and xf.cfxfjbz='1' then 1 else 0 end)as cf5lfcjjc,
38 sum(case when xf.xfxs='200' and( xf.xfrs>=5 and xf.xfrs<=49) and xf.cfxfjbz='1' then xf.xfrs else 0 end)as cf5lfcjrc,
39 sum(case when xf.xfxs='200' and( xf.xfrs>=50 and xf.xfrs<=499) and xf.cfxfjbz='1' then 1 else 0 end)as cf50lfcjjc,
40 sum(case when xf.xfxs='200' and( xf.xfrs>=50 and xf.xfrs<=499) and xf.cfxfjbz='1' then  xf.xfrs else 0 end)as cf50lfcjrc,
41 sum(case when xf.xfxs='200' and xf.xfrs>=500 and xf.cfxfjbz='1' then 1 else 0 end)as cf500lfcjjc,
42 sum(case when xf.xfxs='200' and xf.xfrs>=500 and xf.cfxfjbz='1' then  xf.xfrs else 0 end)as cf500lfcjrc,
43 sum(case when xf.xfxs='200' then 1 else 0 end)as lxxiaojjc,
44 sum(case when xf.xfxs='200' then xf.xfrs else 0 end)as lxxiaojrc,
45 sum(case when xf.xfxs in('300','303') and xf.cfxfjbz='0'and xf.lmbz='0' then 1 else 0 end) as grts,
46 sum(case when xf.xfxs in('300','303') and xf.cfxfjbz='0'and xf.lmbz='1' then 1 else 0 end) as lmts,
47 
48 sum(case when xf.xfxs in('300','303') and xf.cfxfjbz='1'and xf.lmbz='0' then 1 else 0 end) as cfgrts,
49 
50 sum(case when xf.xfxs in('300','303') and xf.cfxfjbz='1'and xf.lmbz='1' then 1 else 0 end) as cflmts,
51 sum(case when xf.xfxs in('300','303') then 1 else 0 end) as xiaoji
52 
53   from hr_organization l,A_TJBB_quxzbmjdqzxfqk xf where  l.jglb='1200' and  l.region_dm='500101' and l.bh=xf.djjgbh group by l.mc;
54 
55   end bb_quxzbmjdqzxfqk_ces;
更多相关文章

随机推荐
