时间：2021-07-01 10:21:17 帮助过：32人阅读
下面是我最近开发当中遇到的一些复杂的SQL的语句如何处理以及优化查询我还在找确切的办法来进行解决。还在进行中，当然对于数据库确实我进行一定的处理，还是有一定的效果的。下面就进入正题吧！
2.花了2天时间写的SQL查询月结算历史的数据
1 select
2 sum(case when indentdate >= ‘2015-11-28 00:00:00‘ and
3 indentdate <= ‘2015-11-28 23:59:59‘ and
4 indenttype = 0 and indent_step = ‘00‘ then 1 else 0 end) totalcount1, --本月总数量
5 convert(int,sum(case when indentdate >= ‘2015-11-28 00:00:00‘ and
6 indentdate <= ‘2015-11-28 23:59:59‘ and
7 indenttype = 0 and indent_step = ‘00‘ then
8 t1.totalpay else 0 end)) totalpay1 ,---本月总金额
9 sum(case when indentdate >=‘2015-11-28 00:00:00‘ and
10 t1.indentdate <= ‘2015-11-28 23:59:59‘ and
11 t2.modifieddate >= ‘2015-11-28 00:00:00‘ and
12 t2.modifieddate <= ‘2015-11-28 23:59:59‘ and
13 t1.indentstatus=‘020‘ and indenttype = 0 and indent_step = ‘00‘ then
14 1 else 0 end)
15 + sum(case when indentdate >=‘2015-11-28 00:00:00‘ and
16 indentdate <= ‘2015-11-28 23:59:59‘ and t1.indentstatus=‘050‘
17 and financedate >= ‘2015-11-28 00:00:00‘ and
18 financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
19 indent_step = ‘00‘ then
20 1 else 0 end)+sum(case when indentdate >=‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘ and financedate>=‘2015-11-28 00:00:00‘ and financedate<=‘2015-11-28 23:59:59‘ and indentstatus IN (‘111‘,‘112‘) and indenttype=1 then 1 else 0 end) totalcount2,--本月失效数量
21 convert(int,sum(case when indentdate >=‘2015-11-28 00:00:00‘ and
22 t1.indentdate <= ‘2015-11-28 23:59:59‘ and t2.modifieddate >= ‘2015-11-28 00:00:00‘ and
23 t2.modifieddate <= ‘2015-11-28 23:59:59‘ and t1.indentstatus=‘020‘
24 and indenttype = 0 and
25 indent_step = ‘00‘ then
26 t1.totalpay else 0 end)
27 + sum(case when indentdate >=‘2015-11-28 00:00:00‘ and
28 indentdate <= ‘2015-11-28 23:59:59‘ and t1.indentstatus=‘050‘
29 and financedate >= ‘2015-11-28 00:00:00‘ and
30 financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
31 indent_step = ‘00‘ then
32 t1.totalpay else 0 end))-sum(case when indentdate >=‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘ and financedate>=‘2015-11-28 00:00:00‘ and t1.financedate<=‘2015-11-28 23:59:59‘ and t1.indentstatus IN (‘111‘,‘112‘) and indenttype=1 then t1.totalpay else 0 end ) totalpay2, ---本月失效金额
33
34 sum(case when t1.indentdate<= ‘2015-11-28 00:00:00‘ and t2.modifieddate >= ‘2015-11-28 00:00:00‘ and
35 t2.modifieddate <= ‘2015-11-28 23:59:59‘ and t1.indentstatus=‘020‘
36 and indenttype = 0 and
37 indent_step = ‘00‘ then
38 1 else 0 end)
39 + sum(case when
40 indentdate <= ‘2015-11-28 00:00:00‘ and t1.indentstatus=‘050‘
41 and financedate >= ‘2015-11-28 00:00:00‘ and
42 financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
43 indent_step = ‘00‘ then
44 1
45 else
46 0
47 --------以上
48 end)+sum(case when indentdate <= ‘2015-11-28 00:00:00‘ and financedate>=‘2015-11-28 00:00:00‘ and financedate<=‘2015-11-28 23:59:59‘ and indentstatus IN (‘111‘,‘112‘) and indenttype=1 then 1 else 0 end) totalcount3 ,---历史失效数量
49 convert(int,sum(case
50 when t1.indentdate<= ‘2015-11-28 00:00:00‘ and t2.modifieddate >= ‘2015-11-28 00:00:00‘ and
51 t2.modifieddate <= ‘2015-11-28 23:59:59‘ and t1.indentstatus=‘020‘
52 and indenttype = 0 and
53 indent_step = ‘00‘ then
54 t1.totalpay
55 else
56 0
57 end)+
58 sum(case
59 when
60 indentdate <= ‘2015-11-28 00:00:00‘ and t1.indentstatus=‘050‘
61 and financedate >= ‘2015-11-28 00:00:00‘ and
62 financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
63 indent_step = ‘00‘ then
64 t1.totalpay
65 else
66 0
67 end) -sum(case when indentdate <= ‘2015-11-28 00:00:00‘ and financedate>=‘2015-11-28 00:00:00‘ and financedate<=‘2015-11-28 23:59:59‘ and t1.indentstatus IN (‘111‘,‘112‘) and indenttype=1 then t1.totalpay else 0 end )) totalpay3, --历史失效金额
68 sum(case
69 when indentdate >= ‘2015-11-28 00:00:00‘ and
70 indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
71 indent_step = ‘00‘ then
72 1
73 else
74 0
75 end) -
76 (sum(case
77 when indentdate >= ‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘and t2.modifieddate>= ‘2015-11-28 00:00:00‘ and
78 t2.modifieddate <= ‘2015-11-28 23:59:59‘
79 and t1.indentstatus=‘020‘ and indenttype = 0 and
80 indent_step = ‘00‘ then
81 1
82 else
83 0
84 end)
85 +sum(case
86 when
87 indentdate >= ‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘ and
88 t1.indentstatus=‘050‘ and financedate >= ‘2015-11-28 00:00:00‘ and
89 financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
90 indent_step = ‘00‘ then
91 1
92 else
93 0
94 end)+sum(case
95 when
96 indentdate >= ‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘ and t1.indentstatus IN (‘111‘,‘112‘) and financedate >= ‘2015-11-28 00:00:00‘ and
97 financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 1 then
98 1
99 else
100 0
101 end)) totalcount4,---本月应结算订单数=本月总订单数-本月失效订单数
102 convert(int,sum(case
103 when indentdate >= ‘2015-11-28 00:00:00‘ and
104 indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
105 indent_step = ‘00‘ then
106 t1.totalpay
107 else
108 0
109 end) -
110 sum(case
111 when indentdate >= ‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘and t2.modifieddate>= ‘2015-11-28 00:00:00‘ and
112 t2.modifieddate <= ‘2015-11-28 23:59:59‘
113 and t1.indentstatus=‘020‘ and indenttype = 0 and
114 indent_step = ‘00‘ then
115 t1.totalpay
116 else
117 0
118 end)
119 -sum(case
120 when
121 indentdate >= ‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘ and
122 t1.indentstatus=‘050‘ and financedate >= ‘2015-11-28 00:00:00‘ and
123 financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
124 indent_step = ‘00‘ then
125 t1.totalpay
126 else
127 0
128 end)+sum(case
129 when
130 indentdate >= ‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘ and t1.indentstatus IN (‘111‘,‘112‘) and financedate >= ‘2015-11-28 00:00:00‘ and
131 financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 1 then
132 t1.totalpay
133 else
134 0
135 end)) totalpay4, --应结算金额
136
137 sum(case
138 when indentdate >= ‘2015-11-28 00:00:00‘ and
139 indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
140 indent_step = ‘00‘ and status=2 then
141 1
142 else
143 0
144 end) -
145 sum(case
146 when t2.modifieddate>= ‘2015-11-28 00:00:00‘ and
147 t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and
148 curstep_id = ‘2A9B4B‘ and status=2 and indenttype = 0 and
149 indent_step = ‘00‘ then
150 1
151 else
152 0
153 end)
154 -sum(case
155 when
156 indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and
157 curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and
158 financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 0 and
159 indent_step = ‘00‘ then
160 1
161 else
162 0
163 end)-sum(case
164 when
165 workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and
166 financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 1 then
167 1
168 else
169 0
170 end) totalcount5,
171
172 convert(int,sum(case
173 when indentdate >= ‘2015-11-28 00:00:00‘ and
174 indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
175 indent_step = ‘00‘ and status=2 then
176 cust_partner_value
177 else
178 0
179 end) -
180 sum(case
181 when t2.modifieddate>= ‘2015-11-28 00:00:00‘ and
182 t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and
183 curstep_id = ‘2A9B4B‘ and status=2 and indenttype = 0 and
184 indent_step = ‘00‘ then
185 cust_partner_value
186 else
187 0
188 end)
189 -sum(case
190 when
191 indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and
192 curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and
193 financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 0 and
194 indent_step = ‘00‘ then
195 cust_partner_value
196 else
197 0
198 end)-sum(case
199 when
200 workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and
201 financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 1 then
202 cust_partner_value
203 else
204 0
205 end)) totalpay5,
206 sum(case
207 when indentdate >= ‘2015-11-28 00:00:00‘ and
208 indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
209 indent_step = ‘00‘ then
210 1
211 else
212 0
213 end) -
214 sum(case
215 when t2.modifieddate>= ‘2015-11-28 00:00:00‘ and
216 t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and
217 curstep_id = ‘2A9B4B‘ and indenttype = 0 and
218 indent_step = ‘00‘ then
219 1
220 else
221 0
222 end)
223 -sum(case
224 when
225 indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and
226 curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and
227 financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
228 indent_step = ‘00‘ then
229 1
230 else
231 0
232 end)-sum(case
233 when
234 workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and
235 financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 1 then
236 1
237 else
238 0
239 end) - (sum(case
240 when indentdate >= ‘2015-11-28 00:00:00‘ and
241 indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
242 indent_step = ‘00‘ and status=2 then
243 1
244 else
245 0
246 end) -
247 sum(case
248 when t2.modifieddate>= ‘2015-11-28 00:00:00‘ and
249 t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and
250 curstep_id = ‘2A9B4B‘ and status=2 and indenttype = 0 and
251 indent_step = ‘00‘ then
252 1
253 else
254 0
255 end)
256 -sum(case
257 when
258 indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and
259 curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and
260 financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 0 and
261 indent_step = ‘00‘ then
262 1
263 else
264 0
265 end)-sum(case
266 when
267 workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and
268 financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 1 then
269 1
270 else
271 0
272 end)) totalcount6,
273 convert(int,sum(case
274 when indentdate >= ‘2015-11-28 00:00:00‘ and
275 indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
276 indent_step = ‘00‘ then
277 cust_partner_value
278 else
279 0
280 end) -
281 sum(case
282 when t2.modifieddate>= ‘2015-11-28 00:00:00‘ and
283 t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and
284 curstep_id = ‘2A9B4B‘ and indenttype = 0 and
285 indent_step = ‘00‘ then
286 cust_partner_value
287 else
288 0
289 end)
290 -sum(case
291 when
292 indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and
293 curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and
294 financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
295 indent_step = ‘00‘ then
296 cust_partner_value
297 else
298 0
299 end)-sum(case
300 when
301 workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and
302 financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 1 then
303 cust_partner_value
304 else
305 0
306 end)-(sum(case
307 when indentdate >= ‘2015-11-28 00:00:00‘ and
308 indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and
309 indent_step = ‘00‘ and status=2 then
310 cust_partner_value
311 else
312 0
313 end) -