1.最近在开发中遇到的一些关于几百行SQL语句做查询的问题，需要如何的解决优化SQL这确实是个问题，对于当下的ORM 框架 EF 以及其他的一些的开源的框架例如Drapper ,以及Sqlite-Sugar 等等，对于查询的速度以及性能确实还不错，但是对于几百条的SQL语句那么可能就不行了这些轻量级的框架扛不住。当在写SQL语句需要注意的规则都无法提高速率的时候,个人认为还是需要传统的ADO.NET 参数化的SQL来进行解决问题。
下面是我最近开发当中遇到的一些复杂的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) -
314 sum(case
315 when t2.modifieddate>= '2015-11-28 00:00:00' and
316 t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and
317 curstep_id = '2A9B4B' and status=2 and indenttype = 0 and
318 indent_step = '00' then
319 cust_partner_value
320 else
321 0
322 end)
323 -sum(case
324 when
325 indentdate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and
326 curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and
327 financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 0 and
328 indent_step = '00' then
329 cust_partner_value
330 else
331 0
332 end)-sum(case
333 when
334 workorderstatus = 'CLOSE' and financedate >= '2015-11-28 00:00:00' and
335 financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 1 then
336 cust_partner_value
337 else
338 0
339 end))) totalpay6
340 from tabcindent t1 left join workorder2 t2 on t1.relation_id=t2.workorder_guid
341 --left join customer t4 on t1.customer_guid = t4.customer_guid
342 left join tvmedia t3 on t1.cust_media_id=t3.idkey --and t1.cust_partner_guid=t3.partner_guid
343 where t1.web_flag=1 and cust_media_id in ('67B3CB84-81F4-87AA-01EB-857EA1474223','CFC5A634-2375-1552-59B4-9A1263DCFCA4','673473E7-8079-68ED-3CB6-9A2256E34A67','E6192562-FCF8-415C-0AC4-9A22A6200706','542CF17F-374E-627D-389B-9A22F09BC4D3','A270E30B-368B-F962-F44F-AA0D76E8865E')
2.通过SQL语句进行查询当天结算历史的记录
1 select
2 convert(int,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
5 t1.totalpay else 0 end)) pay1 ,
6 convert(int,sum(case when t1.indentdate>= '2015-11-28 00:00:00' and
7 t1.indentdate <= '2015-11-28 23:59:59' and t2.modifieddate >= '2015-11-28 00:00:00' and
8 t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and
9 curstep_id = '2A9B4B' and indenttype = 0 and
10 indent_step = '00' then
11 t1.totalpay else 0 end)
12 + sum(case when t1.indentdate>= '2015-11-28 00:00:00' and
13 indentdate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and
14 curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and
15 financedate <= '2015-11-28 23:59:59' and indenttype = 0 and
16 indent_step = '00' then
17 t1.totalpay else 0 end)) +
18 convert(int,sum(case
19 when t1.indentdate<= '2015-11-28 00:00:00' and t2.modifieddate >= '2015-11-28 00:00:00' and
20 t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and
21 curstep_id = '2A9B4B' and indenttype = 0 and
22 indent_step = '00' then
23 t1.totalpay
24 else
25 0
26 end)+
27 sum(case
28 when
29 indentdate <= '2015-11-28 00:00:00' and workorderstatus = 'FAILED' and
30 curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and
31 financedate <= '2015-11-28 23:59:59' and indenttype = 0 and
32 indent_step = '00' then
33 t1.totalpay
34 else
35 0
36 end)-sum(case
37 when
38 workorderstatus = 'CLOSE' and financedate >= '2015-11-28 00:00:00' and
39 financedate <= '2015-11-28 23:59:59' and indenttype = 1 then
40 t1.totalpay
41 else
42 0
43 end)) pay2, --历史失效金额
44 convert(int,sum(case
45 when indentdate >= '2015-11-28 00:00:00' and
46 indentdate <= '2015-11-28 23:59:59' and indenttype = 0 and
47 indent_step = '00' then
48 cust_partner_value
49 else
50 0
51 end) -
52 sum(case
53 when t2.modifieddate>= '2015-11-28 00:00:00' and
54 t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and
55 curstep_id = '2A9B4B' and indenttype = 0 and
56 indent_step = '00' then
57 cust_partner_value
58 else
59 0
60 end)
61 -sum(case
62 when
63 indentdate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and
64 curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and
65 financedate <= '2015-11-28 23:59:59' and indenttype = 0 and
66 indent_step = '00' then
67 cust_partner_value
68 else
69 0
70 end)-sum(case
71 when
72 workorderstatus = 'CLOSE' and financedate >= '2015-11-28 00:00:00' and
73 financedate <= '2015-11-28 23:59:59' and indenttype = 1 then
74 cust_partner_value
75 else
76 0
77 end)-(sum(case
78 when indentdate >= '2015-11-28 00:00:00' and
79 indentdate <= '2015-11-28 23:59:59' and indenttype = 0 and
80 indent_step = '00' and status=2 then
81 cust_partner_value
82 else
83 0
84 end) -
85 sum(case
86 when t2.modifieddate>= '2015-11-28 00:00:00' and
87 t2.modifieddate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and
88 curstep_id = '2A9B4B' and status=2 and indenttype = 0 and
89 indent_step = '00' then
90 cust_partner_value
91 else
92 0
93 end)
94 -sum(case
95 when
96 indentdate <= '2015-11-28 23:59:59' and workorderstatus = 'FAILED' and
97 curstep_id = '5CB9E0' and financedate >= '2015-11-28 00:00:00' and
98 financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 0 and
99 indent_step = '00' then
100 cust_partner_value
101 else
102 0
103 end)-sum(case
104 when
105 workorderstatus = 'CLOSE' and financedate >= '2015-11-28 00:00:00' and
106 financedate <= '2015-11-28 23:59:59' and status=2 and indenttype = 1 then
107 cust_partner_value
108 else
109 0
110 end))) pay4
111 from tabcindent t1 left join workorder2 t2 on t1.relation_id=t2.workorder_guid
112 --left join customer t4 on t1.customer_guid = t4.customer_guid
113 left join tvmedia t3 on t1.cust_media_id=t3.idkey --and t1.cust_partner_guid=t3.partner_guid
114 where t1.web_flag=1 and cust_media_id in ('CFC5A634-2375-1552-59B4-9A1263DCFCA4')
3.对于这样的复杂的SQL进行大数据量的查询如何优化SQL确实是一个问题，但是我通常是这样做的，虽然没有办法解决根本问题但是，效果还是有的。
4.通常我会建立需要的索引，来增加查询的速度。尽量的避免内嵌的查询因为这真的是影响效率。
5.那么当这些工作都做完后优化的作用不大了，那么我通常会在数据库上面进行动手脚，建立数据库集群进行数据库的读写的分离，然后进行建立数据库快照进行数据库的数据的映射。
6.如果此时的方法不行那么创建分区，以及建立临时表倒是一个不错的选择。
7.尽量的避免表与表之间过多的交差，此时宁愿数据库中的表格的字段冗余一些，也不要太多的交差，JOIN ,LEFT JOIN 真的影响查询的效率。
8.通过上面描述的方法，优化后数据库的表的结构以及数据库几百行的SQL语句查询的效率确实变快了。只不过折磨多的SQL语句只能通过
创建存储过程了。然后在应用ADO.NET 参数化SQL 进行访问了。
9.如果您有好的方法可以随时的交流，毕竟我对于数据库方面的经验还不足。
以上内容，全部都是原创，如需转载，请标明！谢谢！
1150
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
