oracle报表总结

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/GZ946/article/details/78574233

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

 

 

 

 


阅读更多

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