前几天客户提出一个月报，经过了解需求及公式等过程长达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
- 2楼runliuv
- 一条SQL 用APPEND，这有多无聊。
- Re: 腰苏图的学习之路
- @runliuv，这是公司代码生成器生成的
- 1楼古芗
- 这么长的SQL.运行效率怎样?
- Re: 腰苏图的学习之路
- @古芗，应该还可以，执行时间大概在0.4秒左右