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

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