IT技术互动交流平台

如何对于几百行SQL语句进行优化?

作者:LowKeyC  来源:IT165收集  发布日期:2016-12-05 20:46:59

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                                                                       
				
Tag标签: 语句  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规