前幾天客戶提出一個月報，經過了解需求及公式等過程長達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 sumjingz 6 from qy_fahb fh 7 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') dd 4 from dual
效果： 
三、求冪： 1 select power(2,3) from dual
