Jelly

一切皆有可能

oracle报表总结

1.按照列合计

select nvl(to_char(t.cjsj,'MM'),'合计'),sum(t.hwl) ,sum(t.zhwl) fromFW_TYSL_95598 t GROUPBYROLLUP(to_char(t.cjsj,'MM'))

示意图


2.静态列转行

select 
    Student,
    sum(decode(Course, '数学', Score)) 数学,
    sum(decode(Course, '物理', Score)) 物理,
    sum(decode(Course, '英语', Score)) 英语,
    sum(decode(Course, '语文', Score)) 语文
from 
    TEST 
group by Student

3. 动态列转行

 

 

 

CREATEORREPLACEPROCEDUREGETBYGDZLQW IS

 

  V_SQL VARCHAR2(2000);

  V_S NUMBER(10);

  CURSOR CURSOR_1 ISSELECTDISTINCT zlmc FROM FW_KFYWZL WHEREywlbdm='04'AND rownum<6  ORDERBYzlmc;

   

    BEGIN

    

      V_SQL := 'select b.zbzlmc';

    

 

     FOR V_L IN CURSOR_1

     LOOP

      V_SQL:= V_SQL|| ',' || '' || V_L.zlmc ||'';

     ENDLOOP;

     V_SQL:= V_SQL|| ',' ||' HJfrom

  (SELECT ';

 

      FOR V_XCLCK IN CURSOR_1

      LOOP

        V_SQL :=V_SQL  || 'SUM(DECODE(zlmc,''' || V_XCLCK.zlmc ||

                 ''',1,0)) AS ' || V_XCLCK.zlmc || ',';

      ENDLOOP;

      V_S:=0;

      FOR V_G IN CURSOR_1

      LOOP

      V_S:=V_S+1;

      

      IF V_S<5THEN

       V_SQL :=V_SQL  || 'SUM(DECODE(zlmc,''' || V_G.zlmc ||

                 ''',1,0)) ' || '+';

      ELSE

         V_SQL :=V_SQL  || 'SUM(DECODE(zlmc,''' || V_G.zlmc ||

                 ''',1,0)) '  ;

    

       ENDIF;

     

      ENDLOOP;

     

     

       V_SQL :=V_SQL|| 'HJ';

     

      V_SQL := V_SQL|| ',' || '(SELECT X.zbzlmc FROM fw_dpzbdyys X WHERE x.zblx=''FW-BB'' AND X.zbzldm =SUBSTR(A.SSGDDW, 1, 4)) gddw

                 

                    FROM FW_KFGDXX A ,fw_kfywzl b

                    WHERE

                     A.YWZLBS=b.kfywzlbs

                     AND A.ywlbdm=''04''

              

                     GROUP BY  SUBSTR(A.SSGDDW, 1,4)    

                     ORDER BY SUBSTR(A.SSGDDW, 1, 4))a,          

                 (SELECT * FROM fw_dpzbdyys WHERE zblx=''FW-BB'' ORDER BY zbzldm) b

                  WHERE a.gddw(+)=b.zbzlmc

           order byb.zbzldm';

     DBMS_OUTPUT.PUT_LINE(V_SQL);

      V_SQL := 'CREATE OR REPLACE VIEW RESULT  AS '||  V_SQL;

      --DBMS_OUTPUT.PUT_LINE(V_SQL);

      EXECUTEIMMEDIATE V_SQL;

    END;

 生成sql语句

SELECTB.ZBZLMC,

       抄表时间调整有异议,

       催缴单内容有异议,

       催缴单派发不及时,

       催缴单派发方式有异议,

       错峰安排有异议,

       HJ

  FROM (SELECTSUM(DECODE(ZLMC,'抄表时间调整有异议', 1, 0)) AS抄表时间调整有异议,

               SUM(DECODE(ZLMC, '催缴单内容有异议', 1, 0)) AS催缴单内容有异议,

               SUM(DECODE(ZLMC, '催缴单派发不及时', 1, 0)) AS催缴单派发不及时,

               SUM(DECODE(ZLMC, '催缴单派发方式有异议', 1, 0)) AS催缴单派发方式有异议,

               SUM(DECODE(ZLMC, '错峰安排有异议', 1, 0)) AS错峰安排有异议,

               SUM(DECODE(ZLMC, '抄表时间调整有异议', 1, 0)) +

               SUM(DECODE(ZLMC, '催缴单内容有异议', 1, 0)) +

               SUM(DECODE(ZLMC, '催缴单派发不及时', 1, 0)) +

               SUM(DECODE(ZLMC, '催缴单派发方式有异议', 1, 0)) +

               SUM(DECODE(ZLMC, '错峰安排有异议', 1, 0))HJ,

               (SELECT X.ZBZLMC

                  FROM FW_DPZBDYYS X

                 WHERE X.ZBLX = 'FW-BB'

                   AND X.ZBZLDM = SUBSTR(A.SSGDDW, 1, 4)) GDDW

       

          FROM FW_KFGDXX A, FW_KFYWZL B

         WHERE A.YWZLBS = B.KFYWZLBS

           AND A.YWLBDM = '04'

       

         GROUPBY SUBSTR(A.SSGDDW, 1, 4)

         ORDERBY SUBSTR(A.SSGDDW, 1, 4)) A,

       (SELECT * FROM FW_DPZBDYYS WHEREZBLX = 'FW-BB'ORDERBY ZBZLDM) B

 WHERE A.GDDW(+) = B.ZBZLMC

 ORDERBY B.ZBZLDM

 

 

 

 


阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/GZ946/article/details/78574233
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭