时间：2021-07-01 10:21:17 帮助过：26人阅读
那么可能就不行了。当在写SQL语句需要注意的规则都无法提高速率的时候,个人认为还是需要传统的ADO.NET 参数化的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