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.如果您有好的方法可以随时的交流，毕竟我对于数据库方面的经验还不足。
以上内容，全部都是原创，如需转载，请标明！谢谢！