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

发布时间:2016-12-6 10:49:19 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"oracle大型汇总报表写法及技巧",主要涉及到oracle大型汇总报表写法及技巧方面的内容,对于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  

上一篇:ACE线程管理机制-面向对象的线程类ACE_Task
下一篇:->

相关文章

相关评论