oracle大型汇总报表写法及技巧

1.max()求最大值,to_number(x)将x列的值变成数字

select max(to_number(t.f_629297609413340))   FROM DEFINE_628653665704647 t

2.设定列的值必须为存在，若为空，则就将其设置为0

nvl(sum(f_629153532773903), 0)

3.decode字句来判断（相当与三元表达式？），允许存在判断，分母不为0则进行除法运算，否则设定结果为0，并以百分比保留两位小数的形式显示。||作为连接符号

 round(decode(qnljtotal, 0, 0, (jnljtotal -qnljtotal) / qnljtotal),4)*100||'%'

4.整体sql结构是按照嵌套形式

select * from (select * from tableA

                )A,

                (...

                )B,

                (..

                )C,

                (..

                )D,

...

where A.a=B.a  and ...

5.设定列的显示值,指定列的特定值

select jn 本月 , qn 上年同期 , 'XX路局' as tylj from tableA

6.AB两表记录合并时，记录条数以多的为标准，记录少的以空填充,想以那个为标准就放到前面，在末尾加上（+）

where luju.A = fy.B(+)

而且必须符合这种格式

    Stringjt_huizong_sql = "SELECT"+

    "        COUNT(F_500696467191640)"+

    "       ,zz.value"+

    "   FROM"+

    "       ("+

    "           SELECT"+

    "                   F_500696467191640"+

    "               FROM"+

    "                   DEFINE_18027099115506"+

    "               WHERE"+

    "                   F_19360672616040 = 'XX公司'"+

    "       )"+

    "       ,("+

    "           SELECT"+

    "                   value"+

    "               FROM"+

    "                       t_sys_code_value"+

    "               WHERE"+

    "                   code_id = '4028810634e06f190134e4094ad500b9'"+

    "        )zz"+

    "   WHERE"+

    "       zz.value = F_500696467191640(+)"+

    "    GROUPBY"+

    "       zz.value";

7.按照指定的顺序排列，需要指定列和顺序

order by decode(jn.lj,'XX南区',1,'XX北区',2,'XX路局',3,'XX路局',4)

8.case when 字句来判断

select case when 条件1 then 值1 else when 条件2 then 值2 else 值3 end as 字段别名

from table

9.将时间直接转换为年月日周季度...

季度：to_char(to_date(f_2710025473791766,'yyyy/MM/dd'), 'yyyy-Q')

周：to_char(to_date(f_2710025473791766,'yyyy/MM/dd'), 'yyyy-IW')

10.同比、环比

1        selectsum(casewhen xsdate between to_date('2010-10-7','yyyy-mm-dd') andto_date('2010-10-8','yyyy-mm-dd') then xsje else0end) "本期销售额",

  2        sum(casewhen xsdate between add_months(to_date('2010-10-7','yyyy-mm-dd'),-12) and add_months(to_date('2010-10-8','yyyy-mm-dd'),-12) then xsje else0end) "同比销售额",

  3        sum(casewhen xsdate between add_months(to_date('2010-10-7','yyyy-mm-dd'),-1) and add_months(to_date('2010-10-8','yyyy-mm-dd'),-1) then xsje else0end) "环比销售额

4   from xsb

  5  where xsdate between to_date('2010-10-7','yyyy-mm-dd') andto_date('2010-10-8','yyyy-mm-dd')

  6    or xsdate between add_months(to_date('2010-10-7','yyyy-mm-dd'),-1) and add_months(to_date('2010-10-8','yyyy-mm-dd'),-1)

  7    or xsdate between add_months(to_date('2010-10-7','yyyy-mm-dd'),-12) and add_months(to_date('2010-10-8','yyyy-mm-dd'),-12);

11.同比环比

    SELECT

            f_2710025473791766

            ,f_9015659754374204

            ,TO_DATE(f_2710025473791766, 'yyyy-mm-dd')

            ,TO_DATE('2011-10-7', 'yyyy-mm-dd')

            ,TO_DATE('2011-12-8', 'yyyy-mm-dd')

            ,SUM(CASE

                WHENTO_DATE(f_2710025473791766, 'yyyy-mm-dd') BETWEENTO_DATE('2011-10-7', 'yyyy-mm-dd') ANDTO_DATE('2011-12-8', 'yyyy-mm-dd') THENTO_NUMBER(f_9015659754374204)

                ELSE 0

            END) "本期销售额"

            ,SUM(CASE

                WHENTO_DATE(f_2710025473791766, 'yyyy-mm-dd') BETWEENADD_MONTHS(TO_DATE('2010-10-7', 'yyyy-mm-dd'), -12) ANDADD_MONTHS(TO_DATE('2010-10-8', 'yyyy-mm-dd'), -12) THENTO_NUMBER(f_9015659754374204)

                ELSE 0

            END) "同比销售额"

            ,SUM(CASE

                WHENTO_DATE(f_2710025473791766, 'yyyy-mm-dd') BETWEENADD_MONTHS(TO_DATE('2010-10-7', 'yyyy-mm-dd'), -1) ANDADD_MONTHS(TO_DATE('2010-10-8', 'yyyy-mm-dd'), -1) THENTO_NUMBER(f_9015659754374204)

                ELSE 0

            END) "环比销售额"

        FROM

            DEFINE_9015449917435662

        WHERE

            TO_DATE(f_2710025473791766, 'yyyy-mm-dd') BETWEENTO_DATE('2011-10-7', 'yyyy-mm-dd')ANDTO_DATE('2011-12-8', 'yyyy-mm-dd')

            ORTO_DATE(f_2710025473791766, 'yyyy-mm-dd') BETWEENADD_MONTHS(TO_DATE('2011-10-7', 'yyyy-mm-dd'), -1) ANDADD_MONTHS(TO_DATE('2011-12-8', 'yyyy-mm-dd'), -1)

            ORTO_DATE(f_2710025473791766, 'yyyy-mm-dd') BETWEENADD_MONTHS(TO_DATE('2011-10-7', 'yyyy-mm-dd'), -12) ANDADD_MONTHS(TO_DATE('2011-12-8', 'yyyy-mm-dd'), -12)

        GROUPBY

            f_2710025473791766

            ,f_9015659754374204

        ORDERBY

           f_2710025473791766

 

 

SELECT

            *

        FROM

            (

                SELECT

                        1 AS xh

                        ,f_9015678025243238

                        ,SUM(f_9015659754374204) f_9015659754374204

                        ,SUM(f_9015669858045698)f_9015669858045698

                        ,SUM(f_9015685870553478)f_9015685870553478

                        ,SUM(f_9015692383024416)f_9015692383024416

                        ,f_9015764331717434

                        ,f_9015768856239154

                    FROM

                        DEFINE_9015449917435662

                    WHERE

                        f_9015678025243238 = 'XX站'

                        AND f_2710025473791766 >='2011-04'

                        AND f_2710025473791766<= '2012-05'

                    GROUPBY

                        f_9015764331717434

                        ,f_9015768856239154

                        ,f_9015678025243238

            ) jn

            ,(

                SELECT

                        2 AS xh

                        ,f_9015678025243238

                        ,NVL(SUM(f_9015659754374204), 0)f_9015659754374204

                        ,NVL(SUM(f_9015669858045698), 0)f_9015669858045698

                        ,NVL(SUM(f_9015685870553478), 0)f_9015685870553478

                        ,NVL(SUM(f_9015692383024416), 0)f_9015692383024416

                        ,f_9015764331717434

                        ,f_9015768856239154

                    FROM

                        DEFINE_9015449917435662

                    WHERE

                        f_9015678025243238 = 'XX站'

                        AND f_2710025473791766>= '2010-04'

                        AND f_2710025473791766<= '2011-05'

                    GROUPBY

                        f_9015764331717434

                        ,f_9015768856239154

                        ,f_9015678025243238

            ) qn

            ,(

                SELECT

                        3 AS xh

                        ,f_9015678025243238

                        ,NVL(SUM(f_9015659754374204), 0)f_9015659754374204

                        ,NVL(SUM(f_9015669858045698), 0)f_9015669858045698

                        ,NVL(SUM(f_9015685870553478), 0)f_9015685870553478

                        ,NVL(SUM(f_9015692383024416), 0)f_9015692383024416

                        ,f_9015764331717434

                        ,f_9015768856239154

                    FROM

                        DEFINE_9015449917435662

                    WHERE

                        f_9015678025243238 = 'XX站'

                        AND f_2710025473791766>= '2011-03'

                        AND f_2710025473791766<= '2012-04'

                    GROUPBY

                        f_9015764331717434

                        ,f_9015768856239154

                        ,f_9015678025243238

            ) sy

        WHERE

            (

                TO_NUMBER(jn.f_9015764331717434) -1

            ) = qn.f_9015764331717434(+)

            AND jn.f_9015768856239154 =qn.f_9015768856239154(+)

            andcasewhenTO_NUMBER(jn.f_9015768856239154)==12

            then jn.f_9015764331717434 =sy.f_9015764331717434(+) and

            anddecode(TO_NUMBER(jn.f_9015768856239154),1, 12, jn.f_9015768856239154)

           

            AND jn.f_9015764331717434 =sy.f_9015764331717434(+)

            AND (

                TO_NUMBER(jn.f_9015768856239154) -1

            ) = sy.f_9015768856239154(+)

            AND jn.f_9015678025243238 =qn.f_9015678025243238(+)

        ORDERBY

            jn.f_9015764331717434

           ,jn.f_9015768856239154