你的位置:首页 > 数据库

[数据库]Oracle常用几种Sql用法


 

  前几天客户提出一个月报,经过了解需求及公式等过程长达20小时,总算基本模型出来了,贴出来啥晒,对于我这种菜鸟来说也算小有提高,虽然Sql语句不是很庞大,但是里面涉及到了几种算法,个人觉得还是经常能用到的,所以贴出来跟大家分享一下,如果大牛直接跳过!呵呵,废话不多说,先贴几张图:

 1 public DataSet GetRanmcgjhData(string yf) 2     { 3       DataSet ds = new DataSet(); 4       StringBuilder strSql=new StringBuilder(); 5       strSql.Append(" select rjmqkAndcgjh.daohrq_rjmqk,"); 6       strSql.Append("    round(rjmqkAndcgjh.jingz_rjmqk, 2) jingz_rjmqk,"); 7       strSql.Append("    round(sum_jingz / (case rjmqkAndcgjh.sfriq"); 8       strSql.Append("        when 0 then"); 9       strSql.Append("         1"); 10       strSql.Append("        else"); 11       strSql.Append("         rjmqkAndcgjh.sfriq"); 12       strSql.Append("       end),"); 13       strSql.Append("       2) yuejhfjl,"); 14       strSql.Append("    rjmqkAndcgjh.std_rjmqk,"); 15       strSql.Append("    rjmqkAndcgjh.jihl,"); 16       strSql.Append("    rjmqkAndcgjh.rjmqkAndcgjh_qy_jh_meikxxb_fk,"); 17       strSql.Append("    rjmqkAndcgjh.laiml,"); 18       strSql.Append("    rjmqkAndcgjh.jingz_rjmqk,"); 19       strSql.Append("    rjmqkAndcgjh.qnet_ar_rjmqk,"); 20       strSql.Append("    round(rjmqkAndcgjh.jihl_tzh / jh.zb,2) zanb,"); 21       strSql.Append("    jhzxqk.daohrq_jhzxqk,"); 22       strSql.Append("    1zanb_tzh,"); 23       strSql.Append("    rjmqkAndcgjh.jihl_tzh,"); 24       strSql.Append("    rjmqkAndcgjh.qnet_ar,"); 25       strSql.Append("    rjmqkAndcgjh.std,"); 26       strSql.Append("    rjmqkAndcgjh.mt,"); 27       strSql.Append("    rjmqkAndcgjh.huiff,"); 28       strSql.Append("    rjmqkAndcgjh.yunfglj,"); 29       strSql.Append("    round(rjmqkAndcgjh.yunj * rjmqkAndcgjh.yunfglj, 2) yunfdj,"); 30       strSql.Append("    round(rjmqkAndcgjh.qnet_ar * rjmqkAndcgjh.meijkj, 2) meijdj,"); 31       strSql.Append("    round_new(rjmqkAndcgjh.yunj * rjmqkAndcgjh.yunfglj +"); 32       strSql.Append("         rjmqkAndcgjh.qnet_ar * rjmqkAndcgjh.meijkj,"); 33       strSql.Append("         2) zonghymj,"); 34       strSql.Append("    round_new(rjmqkAndcgjh.yunj * rjmqkAndcgjh.yunfglj +"); 35       strSql.Append("         rjmqkAndcgjh.qnet_ar * rjmqkAndcgjh.meijkj /"); 36       strSql.Append("         (rjmqkAndcgjh.qnet_ar / 7000),"); 37       strSql.Append("         2) zonghbmj,"); 38       strSql.Append("    rjmqkAndcgjh.meijkj,"); 39       strSql.Append("    jhzxqk.jingz_jhzxqk,"); 40       strSql.Append("    jhzxqk.qnet_ar_jhzxqk,"); 41       strSql.Append("    jhzxqk.std_jhzxqk,"); 42       strSql.Append("    jhzxqk.mt_jhzxqk,"); 43       strSql.Append("    jhzxqk.mkmingc,"); 44       strSql.Append("    jhzxqk.vdaf_jhzxqk,"); 45       strSql.Append("    (case"); 46       strSql.Append("     when (jhzxqk.qnet_ar_jhzxqk - rjmqkAndcgjh.qnet_ar) >= 0 then"); 47       strSql.Append("      jhzxqk.qnet_ar_jhzxqk * rjmqkAndcgjh.meijkj"); 48       strSql.Append("     else"); 49       strSql.Append("      jhzxqk.qnet_ar_jhzxqk *"); 50       strSql.Append("      (rjmqkAndcgjh.meijkj -"); 51       strSql.Append("      power(2,"); 52       strSql.Append("         FLOOR((rjmqkAndcgjh.qnet_ar - jhzxqk.qnet_ar_jhzxqk) / 100)) / 1000)"); 53       strSql.Append("    end) meij_jhzxqk,"); 54       strSql.Append("    round((case"); 55       strSql.Append("        when (rjmqkAndcgjh.qnet_ar_rjmqk - rjmqkAndcgjh.qnet_ar) >= 0 then"); 56       strSql.Append("         rjmqkAndcgjh.qnet_ar_rjmqk * rjmqkAndcgjh.meijkj"); 57       strSql.Append("        else"); 58       strSql.Append("         rjmqkAndcgjh.qnet_ar_rjmqk *"); 59       strSql.Append("         (rjmqkAndcgjh.meijkj -"); 60       strSql.Append("         power(2,"); 61       strSql.Append("            FLOOR((rjmqkAndcgjh.qnet_ar - rjmqkAndcgjh.qnet_ar_rjmqk) / 100)) / 1000)"); 62       strSql.Append("       end),"); 63       strSql.Append("       2) meij_rjmqk,"); 64       strSql.Append("    round_new(((case"); 65       strSql.Append("          when (rjmqkAndcgjh.qnet_ar_rjmqk - rjmqkAndcgjh.qnet_ar) >= 0 then"); 66       strSql.Append("           rjmqkAndcgjh.qnet_ar_rjmqk * rjmqkAndcgjh.meijkj"); 67       strSql.Append("          else"); 68       strSql.Append("           rjmqkAndcgjh.qnet_ar_rjmqk *"); 69       strSql.Append("           (rjmqkAndcgjh.meijkj -"); 70       strSql.Append("           power(2,"); 71       strSql.Append("              FLOOR((rjmqkAndcgjh.qnet_ar - rjmqkAndcgjh.qnet_ar_rjmqk) / 100)) / 1000)"); 72       strSql.Append("         end) * 7000) / rjmqkAndcgjh.qnet_ar_rjmqk,"); 73       strSql.Append("         2) bmdj_rjmqk,"); 74       strSql.Append("    round(rjmqkAndcgjh.yunj * rjmqkAndcgjh.yunfglj, 2) yunf_jhzxqk,"); 75       strSql.Append("    round((case"); 76       strSql.Append("        when (jhzxqk.qnet_ar_jhzxqk - rjmqkAndcgjh.qnet_ar) >= 0 then"); 77       strSql.Append("         jhzxqk.qnet_ar_jhzxqk * rjmqkAndcgjh.meijkj"); 78       strSql.Append("        else"); 79       strSql.Append("         jhzxqk.qnet_ar_jhzxqk *"); 80       strSql.Append("         (rjmqkAndcgjh.meijkj -"); 81       strSql.Append("         power(2,"); 82       strSql.Append("            FLOOR((rjmqkAndcgjh.qnet_ar - jhzxqk.qnet_ar_jhzxqk) / 100)) / 1000)"); 83       strSql.Append("       end) + rjmqkAndcgjh.yunj * rjmqkAndcgjh.yunfglj,"); 84       strSql.Append("       2) zhymj_jhzxqk,"); 85       strSql.Append("    round_new(((case"); 86       strSql.Append("          when (jhzxqk.qnet_ar_jhzxqk - rjmqkAndcgjh.qnet_ar) >= 0 then"); 87       strSql.Append("           jhzxqk.qnet_ar_jhzxqk * rjmqkAndcgjh.meijkj"); 88       strSql.Append("          else"); 89       strSql.Append("           jhzxqk.qnet_ar_jhzxqk *"); 90       strSql.Append("           (rjmqkAndcgjh.meijkj -"); 91       strSql.Append("           power(2,"); 92       strSql.Append("              FLOOR((rjmqkAndcgjh.qnet_ar - jhzxqk.qnet_ar_jhzxqk) / 100)) / 1000)"); 93       strSql.Append("         end) + rjmqkAndcgjh.yunj * rjmqkAndcgjh.yunfglj) /"); 94       strSql.Append("         (jhzxqk.qnet_ar_jhzxqk / 7000),"); 95       strSql.Append("         2) zhbmj_jhzxqk,"); 96       strSql.Append("    round(jhzxqk.jingz_jhzxqk - rjmqkAndcgjh.jihl * 10000, 2) jingz_zxcyfx,"); 97       strSql.Append("    round_new(jhzxqk.qnet_ar_jhzxqk - rjmqkAndcgjh.qnet_ar, 2) qnet_ar_zxcyfx,"); 98       strSql.Append("    round_new(jhzxqk.std_jhzxqk - rjmqkAndcgjh.std, 2) std_zxcyfx,"); 99       strSql.Append("    round_new(jhzxqk.vdaf_jhzxqk - rjmqkAndcgjh.huiff, 2) vdaf_zxcyfx,");100       strSql.Append("    round_new(jhzxqk.mt_jhzxqk - rjmqkAndcgjh.mt, 2) mt_zxcyfx,");101       strSql.Append("    round_new(rjmqkAndcgjh.jihl_tzh * 10000 / 21, 2) yjhfj,");102       strSql.Append("    round_new((case");103       strSql.Append("          when (jhzxqk.qnet_ar_jhzxqk - rjmqkAndcgjh.qnet_ar) >= 0 then");104       strSql.Append("           jhzxqk.qnet_ar_jhzxqk * rjmqkAndcgjh.meijkj");105       strSql.Append("          else");106       strSql.Append("           jhzxqk.qnet_ar_jhzxqk *");107       strSql.Append("           (rjmqkAndcgjh.meijkj -");108       strSql.Append("           power(2,");109       strSql.Append("              FLOOR((rjmqkAndcgjh.qnet_ar - jhzxqk.qnet_ar_jhzxqk) / 100)) / 1000)");110       strSql.Append("         end) + rjmqkAndcgjh.yunj * rjmqkAndcgjh.yunfglj -");111       strSql.Append("         (rjmqkAndcgjh.yunj * rjmqkAndcgjh.yunfglj +");112       strSql.Append("         rjmqkAndcgjh.qnet_ar * rjmqkAndcgjh.meijkj),");113       strSql.Append("         2) zhymj_zxcyfx,");114       strSql.Append("    round_new(((case");115       strSql.Append("          when (jhzxqk.qnet_ar_jhzxqk - rjmqkAndcgjh.qnet_ar) >= 0 then");116       strSql.Append("           jhzxqk.qnet_ar_jhzxqk * rjmqkAndcgjh.meijkj");117       strSql.Append("          else");118       strSql.Append("           jhzxqk.qnet_ar_jhzxqk *");119       strSql.Append("           (rjmqkAndcgjh.meijkj -");120       strSql.Append("           power(2,");121       strSql.Append("              FLOOR((rjmqkAndcgjh.qnet_ar - jhzxqk.qnet_ar_jhzxqk) / 100)) / 1000)");122       strSql.Append("         end) + rjmqkAndcgjh.yunj * rjmqkAndcgjh.yunfglj) /");123       strSql.Append("         (jhzxqk.qnet_ar_jhzxqk / 7000) -");124       strSql.Append("         rjmqkAndcgjh.qnet_ar * rjmqkAndcgjh.meijkj /");125       strSql.Append("         (rjmqkAndcgjh.qnet_ar / 7000),");126       strSql.Append("         2) zhbmj_zxcyfx");127       strSql.Append("  from (select sum(fh.jingz) jingz_jhzxqk,");128       strSql.Append("        round_new(sum(zl.qnet_ar * fh.jingz) / sum(fh.jingz) /");129       strSql.Append("             0.0041868,");130       strSql.Append("             2) qnet_ar_jhzxqk,");131       strSql.Append("        round_new(sum(zl.std * fh.jingz) / sum(fh.jingz), 2) std_jhzxqk,");132       strSql.Append("        round_new(sum(zl.vdaf * fh.jingz) / sum(fh.jingz), 2) vdaf_jhzxqk,");133       strSql.Append("        round_new(sum(zl.mt * fh.jingz) / sum(fh.jingz), 2) mt_jhzxqk,");134       strSql.Append("        mk.mingc mkmingc,");135       strSql.Append("        fh.qy_jh_meikxxb_fk,");136       strSql.Append("        to_char(fh.daohrq, 'yyyy-MM') daohrq_jhzxqk");137       strSql.Append("      from qy_fahb fh");138       strSql.Append("      left join qy_zhilb zl");139       strSql.Append("       on zl.qy_zhilboid = fh.qy_zhilb_fk");140       strSql.Append("      left join qy_jh_meikxxb mk");141       strSql.Append("       on mk.qy_jh_meikxxboid = fh.qy_jh_meikxxb_fk");142       strSql.Append("     where fh.jingz <> 0");143       strSql.Append("      and zl.qy_zhilboid is not null");144       strSql.Append("     group by mk.mingc,");145       strSql.Append("          to_char(fh.daohrq, 'yyyy-MM'),");146       strSql.Append("          fh.qy_jh_meikxxb_fk) jhzxqk");147       strSql.Append("  left join (select rjmqk.jingz_rjmqk,");148       strSql.Append("           rjmqk.qnet_ar_rjmqk,");149       strSql.Append("           rjmqk.std_rjmqk,");150       strSql.Append("           rjmqk.vdaf_rjmqk,");151       strSql.Append("           rjmqk.qy_jh_meikxxb_fk rjmqkAndcgjh_qy_jh_meikxxb_fk,");152       strSql.Append("           rjmqk.daohrq_rjmqk,");153       strSql.Append("           cgjh.jihl,");154       strSql.Append("           cgjh.jihl_tzh,");155       strSql.Append("           cgjh.qnet_ar,");156       strSql.Append("           cgjh.std,");157       strSql.Append("           cgjh.mt,");158       strSql.Append("           cgjh.huiff,");159       strSql.Append("           cgjh.yunj,");160       strSql.Append("           cgjh.yunfglj,");161       strSql.Append("           jhlrl.laiml,");162       strSql.Append("           cgjh.meijkj,");163       strSql.Append("           (select last_day(to_date(rjmqk.daohrq_rjmqk, 'yyyy-MM-dd')) -");164       strSql.Append("               last_day(add_months(to_date(rjmqk.daohrq_rjmqk,");165       strSql.Append("                             'yyyy-MM-dd'),");166       strSql.Append("                         -1)) -");167       strSql.Append("               to_char(to_date(rjmqk.daohrq_rjmqk, 'yyyy-MM-dd'),");168       strSql.Append("                   'dd') + 1");169       strSql.Append("            from dual) sfriq,");170       strSql.Append("           (cgjh.jihl_tzh * 10000 -");171       strSql.Append("           (sum(rjmqk.jingz_rjmqk)");172       strSql.Append("           over(PARTITION BY rjmqk.qy_jh_meikxxb_fk order by");173       strSql.Append("              rjmqk.daohrq_rjmqk) - rjmqk.jingz_rjmqk)) sum_jingz");174       strSql.Append("        from (select sum(fh.jingz) jingz_rjmqk,");175       strSql.Append("               round_new((sum((round_new(zl.qnet_ar / 0.0041868,");176       strSql.Append("                            2)) * fh.jingz) /");177       strSql.Append("                    sum(fh.jingz)),");178       strSql.Append("                    2) qnet_ar_rjmqk,");179       strSql.Append("               round_new(sum(zl.std * fh.jingz) / sum(fh.jingz),");180       strSql.Append("                    2) std_rjmqk,");181       strSql.Append("               round_new(sum(zl.vdaf * fh.jingz) / sum(fh.jingz),");182       strSql.Append("                    2) vdaf_rjmqk,");183       strSql.Append("               mk.mingc mkmingc,");184       strSql.Append("               fh.qy_jh_meikxxb_fk,");185       strSql.Append("               to_char(fh.daohrq, 'yyyy-MM-dd') daohrq_rjmqk");186       strSql.Append("            from qy_fahb fh");187       strSql.Append("            left join qy_zhilb zl");188       strSql.Append("             on zl.qy_zhilboid = fh.qy_zhilb_fk");189       strSql.Append("            left join qy_jh_meikxxb mk");190       strSql.Append("             on mk.qy_jh_meikxxboid = fh.qy_jh_meikxxb_fk");191       strSql.Append("            where fh.jingz <> 0");192       strSql.Append("             and zl.qy_zhilboid is not null");193       strSql.Append("            group by mk.mingc,");194       strSql.Append("                to_char(fh.daohrq, 'yyyy-MM-dd'),");195       strSql.Append("                fh.qy_jh_meikxxb_fk) rjmqk");196       strSql.Append("        left join qy_yuecgjhb cgjh");197       strSql.Append("         on cgjh.qy_jh_meikxxb_fk = rjmqk.qy_jh_meikxxb_fk");198       strSql.Append("         and to_char(to_date(rjmqk.daohrq_rjmqk, 'yyyy-MM-dd'),");199       strSql.Append("               'yyyy-MM') = to_char(cgjh.yuef, 'yyyy-MM')");200       strSql.Append("        left join qy_jihllrb jhlrl");201       strSql.Append("         on jhlrl.qy_jh_meikxxb_fk = rjmqk.qy_jh_meikxxb_fk");202       strSql.Append("         and to_char(jhlrl.laimrq, 'yyyy-MM-dd') = rjmqk.daohrq_rjmqk");203       strSql.Append("        where to_char(jhlrl.laimrq, 'yyyy-MM') =  '" + yf + "') rjmqkAndcgjh");204       strSql.Append("   on rjmqkAndcgjh.rjmqkAndcgjh_qy_jh_meikxxb_fk = jhzxqk.qy_jh_meikxxb_fk");205       strSql.Append("  and to_char(to_date(rjmqkAndcgjh.daohrq_rjmqk, 'yyyy-MM-dd'), 'yyyy-MM') =");206       strSql.Append("    jhzxqk.daohrq_jhzxqk");207       strSql.Append("  left join (select sum(ycgjh.jihl_tzh) zb,");208       strSql.Append("           to_char(ycgjh.yuef, 'yyyy-MM') yuef");209       strSql.Append("        from qy_yuecgjhb ycgjh");210       strSql.Append("        group by to_char(ycgjh.yuef, 'yyyy-MM')) jh");211       strSql.Append("   on jh.yuef = jhzxqk.daohrq_jhzxqk");212       strSql.Append(" where to_char(to_date(jhzxqk.daohrq_jhzxqk, 'yyyy-MM'), 'yyyy-MM') =");213       strSql.Append("    '"+yf+"'");214       ds = DbHelperOra.Query(strSql.ToString());215       return ds;216     }

 

 

 

一、实现分组累计数据

  先看一下实现的效果:

  

  如上图所示,2014/12/2 日取1日的jingz,3日取1号+2号的累计,一次类推,我相信应该很多人都遇到这样的需求,下面我把我的实现语句分享一下:

1 select fh.daohrq,2     fh.jingz,3     (sum(fh.jingz)4     over(PARTITION BY fh.qy_jh_meikxxb_fk order by fh.daohrq)) -5     fh.jingz sumjingz6  from qy_fahb fh7 where to_char(fh.daohrq, 'yyyy-MM') = '2014-12'8  and fh.qy_jh_meikxxb_fk = 'dc757915-9aa9-4cec-bde2-438cb1c4ca21'

二、当月天数和当天相差天数

1 select to_char(sysdate, 'yyyy-MM-dd') d,2    last_day(sysdate) - last_day(add_months(sysdate, -1)) -3    to_char(sysdate, 'dd') dd4  from dual

效果:

三、求幂:  1 select power(2,3) from dual