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

本是古典何须时尚 2022-07-13 10:57 208阅读 0赞

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.如果您有好的方法可以随时的交流，毕竟我对于数据库方面的经验还不足。

以上内容，全部都是原创，如需转载，请标明！谢谢！