select v.BatchID,sum(DATEDIFF(s,p.ph_end,p.ph_start)) ,sum(DATEDIFF(s,p1.ph_end,p1.ph_start)) from vAPVrsCIPBatchList v left join PhaseHold p on v.BatchID=p.ph_batchid where p.ph_phaseid=8 group by v.BatchIDleft join PhaseHold p1 on v.BatchID=p1.ph_batchid where p.ph_phaseid=2 group by v.BatchID这样写对不?
select v.BatchID,sum(DATEDIFF(s,p.ph_end,p.ph_start)) ,sum(DATEDIFF(s,p1.ph_end,p1.ph_start)) from vAPVrsCIPBatchList v ,PhaseHold p, PhaseHold p1 where v.BatchID*=p.ph_batchid and p.ph_phaseid=8 and v.BatchID*=p1.ph_batchid and p1.ph_phaseid=2 group by v.BatchID
--> 测试数据:[PhaseHold] if object_id('[PhaseHold]') is not null drop table [PhaseHold] create table [PhaseHold]([ph_holdkey] int,[ph_start] datetime,[ph_end] datetime,[ph_phaseid] int,[ph_batchid] int) insert [PhaseHold] select 227,'2012-04-25 17:32:28.710','2012-04-25 17:33:44.100',18,249 union all select 228,'2012-04-25 17:33:57.510','2012-04-25 17:37:33.927',8,249 union all select 229,'2012-04-25 17:38:03.110','2012-04-25 17:40:09.110',18,249 union all select 230,'2012-04-25 18:02:21.327','2012-04-25 18:10:24.880',8,251 union all select 231,'2012-04-25 18:06:29.770','2012-04-25 18:06:38.037',8,250 union all select 232,'2012-04-25 18:06:38.550','2012-04-25 18:07:03.223',8,250 union all select 233,'2012-04-25 18:07:04.663','2012-04-25 18:07:57.770',2,250 union all select 234,'2012-04-25 18:07:58.893','2012-04-25 18:10:39.483',8,250 union all select 235,'2012-04-25 18:10:42.043','2012-04-25 18:10:43.237',8,250 union all select 236,'2012-04-25 18:10:46.117','2012-04-25 18:10:48.250',2,250 union all select 237,'2012-04-25 18:10:49.683','2012-04-25 18:14:18.903',8,250 union all select 238,'2012-04-25 18:14:23.233','2012-04-25 18:16:46.887',2,250 union all select 239,'2012-04-25 18:43:09.563','2012-04-25 18:43:16.743',8,252 union all select 240,'2012-04-25 18:43:22.390','2012-04-25 18:52:04.897',8,252 union all select 241,'2012-04-25 19:21:05.843','2012-04-25 19:23:12.773',8,254 union all select 242,'2012-04-25 19:23:16.383','2012-04-25 19:28:29.387',8,254 union all select 243,'2012-04-25 19:28:35.697','2012-04-25 19:29:50.883',8,254 union all select 244,'2012-04-25 20:00:26.193','2012-04-25 20:00:44.170',8,255 union all select 245,'2012-04-25 20:01:32.403','2012-04-25 20:11:48.747',8,255 union all select 246,'2012-04-25 20:11:51.783','2012-04-25 20:12:49.893',8,255 union all select 247,'2012-04-25 20:48:58.537','2012-04-25 20:49:05.780',8,256 union all select 248,'2012-04-25 20:49:10.067','2012-04-25 20:49:17.130',8,256 union all select 249,'2012-04-25 20:49:34.420','2012-04-25 20:49:52.753',8,256 union all select 250,'2012-04-25 20:50:17.840','2012-04-25 20:57:36.867',8,256 union all select 251,'2012-04-25 21:32:30.710','2012-04-25 21:42:43.910',8,258 union all select 252,'2012-04-25 22:35:38.130','2012-04-25 22:35:41.780',8,259 union all select 253,'2012-04-25 22:35:42.643','2012-04-25 22:42:29.307',8,259 union all select 254,'2012-04-26 09:37:57.020','2012-04-26 09:46:37.150',8,264 union all select 323,'2012-04-26 09:46:37.073','2012-04-26 09:46:37.150',8,264 union all select 337,'2012-04-26 10:22:18.027','2012-04-26 10:50:24.043',8,267 union all select 338,'2012-04-26 16:53:30.607','2012-04-26 17:08:29.910',8,276 union all select 339,'2012-04-26 16:53:46.743','2012-04-26 16:55:19.827',8,277 union all select 340,'2012-04-26 16:55:19.970','2012-04-26 16:55:20.180',8,277 union all select 341,'2012-04-26 16:55:20.317','2012-04-26 16:55:20.453',8,277 union all select 342,'2012-04-26 16:55:20.517','2012-04-26 16:55:20.650',8,277 union all select 343,'2012-04-26 16:55:20.797','2012-04-26 16:55:21.353',8,277--> 测试数据:[vAPVrsCIPBatchList] if object_id('[vAPVrsCIPBatchList]') is not null drop table [vAPVrsCIPBatchList] create table [vAPVrsCIPBatchList]([BatchID] int) insert [vAPVrsCIPBatchList] select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19 union all select 20 union all select 21 union all select 22 union all select 23 union all select 24 union all select 25 union all select 26 union all select 27 union all select 28 union all select 29 union all select 30 union all select 31 union all select 32 union all select 33 union all select 34 union all select 35 union all select 36 union all select 37 union all select 38 union all select 39 union all select 40 union all select 41 union all select 42 union all select 43 union all select 44 union all select 45 union all select 46 union all select 47 union all select 48 union all select 49 union all select 50 union all select 51 union all select 52 union all select 53 union all select 54 union all select 55 union all select 56 union all select 57 union all select 58 union all select 59 union all select 60 union all select 61 union all select 62 union all select 63 union all select 64 union all select 65 union all select 66 union all select 67 union all select 68 union all select 69 union all select 70 union all select 71 union all select 72 union all select 73 union all select 74 union all select 75 union all select 76 union all select 77 union all select 78 union all select 79 union all select 80 union all select 81 union all select 82 union all select 83 union all select 88 union all select 89 union all select 90 union all select 91 union all select 92 union all select 93 union all select 94 union all select 95 union all select 96 union all select 97 union all select 98 union all select 99 union all select 100 union all select 101 union all select 102 union all select 103 union all select 104 union all select 105 union all select 106 union all select 107 union all select 108 union all select 109 union all select 110 union all select 111 union all select 112 union all select 113 union all select 114 union all select 115 union all select 116 union all select 117 union all select 118 union all select 119 union all select 127 union all select 131 union all select 132 union all select 133 union all select 134 union all select 135 union all select 136 union all select 137 union all select 139 union all select 140 union all select 141 union all select 142 union all select 143 union all select 144 union all select 145 union all select 146 union all select 147 union all select 148 union all select 149 union all select 150 union all select 151 union all select 152 union all select 153 union all select 159 union all select 160 union all select 161 union all select 162 union all select 163 union all select 164 union all select 165 union all select 166 union all select 167 union all select 168 union all select 169 union all select 170 union all select 171 union all select 172 union all select 173 union all select 182 union all select 183 union all select 184 union all select 185 union all select 186 union all select 187 union all select 188 union all select 189 union all select 190 union all select 191 union all select 192 union all select 193 union all select 194 union all select 195select b.BatchID,sum(isnull(a.total,0)) total from( select ph_batchid,sum(DATEDIFF(s,ph_end,ph_start)) as total from [PhaseHold] a where exists( select 1 from [vAPVrsCIPBatchList] b where a.ph_batchid=b.BatchID ) and a.ph_phaseid=8 or a.ph_phaseid=2 group by ph_batchid)a right join [vAPVrsCIPBatchList] b on a.ph_batchid=b.BatchID group by b.BatchID /* BatchID total 1 0 2 0 3 0 4 0 5 0 6 0 7 0 8 0 9 0 10 0 11 0 12 0 13 0 14 0 15 0 16 0 17 0 18 0 19 0 20 0 21 0 22 0 23 0 24 0 25 0 26 0 27 0 28 0 29 0 30 0 31 0 32 0 33 0 34 0 35 0 36 0 37 0 38 0 39 0 40 0 41 0 42 0 43 0 44 0 45 0 46 0 47 0 48 0 49 0 50 0 51 0 52 0 53 0 54 0 55 0 56 0 57 0 58 0 59 0 60 0 61 0 62 0 63 0 64 0 65 0 66 0 67 0 68 0 69 0 70 0 71 0 72 0 73 0 74 0 75 0 76 0 77 0 78 0 79 0 80 0 81 0 82 0 83 0 88 0 89 0 90 0 91 0 92 0 93 0 94 0 95 0 96 0 97 0 98 0 99 0 100 0 101 0 102 0 103 0 104 0 105 0 106 0 107 0 108 0 109 0 110 0 111 0 112 0 113 0 114 0 115 0 116 0 117 0 118 0 119 0 127 0 131 0 132 0 133 0 134 0 135 0 136 0 137 0 139 0 140 0 141 0 142 0 143 0 144 0 145 0 146 0 147 0 148 0 149 0 150 0 151 0 152 0 153 0 159 0 160 0 161 0 162 0 163 0 164 0 165 0 166 0 167 0 168 0 169 0 170 0 171 0 172 0 173 0 182 0 183 0 184 0 185 0 186 0 187 0 188 0 189 0 190 0 191 0 192 0 193 0 194 0 195 0 196 0 197 0 198 0 207 0 208 0 209 0 212 0 213 0 214 0 215 0 216 0 217 0 218 0 219 0 220 0 221 0 222 0 223 0 224 0 225 0 226 0 227 0 234 0 235 0 236 0 237 0 238 0 239 0 240 0 241 0 242 0 243 0 244 0 245 0 246 0 247 0 248 0 249 -216 250 -603 251 -483 252 -529 253 0 254 -515 255 -692 256 -471 257 0 258 -613 259 -410 262 0 264 -520 267 -1686 275 0 276 -899 277 -95 278 0 279 0 280 0 281 0 282 0 283 0 284 0 285 0 286 0 287 0 288 0 289 0 290 0 */
from vAPVrsCIPBatchList v
left join PhaseHold p on v.BatchID=p.ph_batchid where p.ph_phaseid=8 group by v.BatchIDleft join PhaseHold p1 on v.BatchID=p1.ph_batchid where p.ph_phaseid=2 group by v.BatchID这样写对不?
from vAPVrsCIPBatchList v ,PhaseHold p, PhaseHold p1 where v.BatchID*=p.ph_batchid and p.ph_phaseid=8 and v.BatchID*=p1.ph_batchid and p1.ph_phaseid=2
group by v.BatchID
--> 测试数据:[PhaseHold]
if object_id('[PhaseHold]') is not null drop table [PhaseHold]
create table [PhaseHold]([ph_holdkey] int,[ph_start] datetime,[ph_end] datetime,[ph_phaseid] int,[ph_batchid] int)
insert [PhaseHold]
select 227,'2012-04-25 17:32:28.710','2012-04-25 17:33:44.100',18,249 union all
select 228,'2012-04-25 17:33:57.510','2012-04-25 17:37:33.927',8,249 union all
select 229,'2012-04-25 17:38:03.110','2012-04-25 17:40:09.110',18,249 union all
select 230,'2012-04-25 18:02:21.327','2012-04-25 18:10:24.880',8,251 union all
select 231,'2012-04-25 18:06:29.770','2012-04-25 18:06:38.037',8,250 union all
select 232,'2012-04-25 18:06:38.550','2012-04-25 18:07:03.223',8,250 union all
select 233,'2012-04-25 18:07:04.663','2012-04-25 18:07:57.770',2,250 union all
select 234,'2012-04-25 18:07:58.893','2012-04-25 18:10:39.483',8,250 union all
select 235,'2012-04-25 18:10:42.043','2012-04-25 18:10:43.237',8,250 union all
select 236,'2012-04-25 18:10:46.117','2012-04-25 18:10:48.250',2,250 union all
select 237,'2012-04-25 18:10:49.683','2012-04-25 18:14:18.903',8,250 union all
select 238,'2012-04-25 18:14:23.233','2012-04-25 18:16:46.887',2,250 union all
select 239,'2012-04-25 18:43:09.563','2012-04-25 18:43:16.743',8,252 union all
select 240,'2012-04-25 18:43:22.390','2012-04-25 18:52:04.897',8,252 union all
select 241,'2012-04-25 19:21:05.843','2012-04-25 19:23:12.773',8,254 union all
select 242,'2012-04-25 19:23:16.383','2012-04-25 19:28:29.387',8,254 union all
select 243,'2012-04-25 19:28:35.697','2012-04-25 19:29:50.883',8,254 union all
select 244,'2012-04-25 20:00:26.193','2012-04-25 20:00:44.170',8,255 union all
select 245,'2012-04-25 20:01:32.403','2012-04-25 20:11:48.747',8,255 union all
select 246,'2012-04-25 20:11:51.783','2012-04-25 20:12:49.893',8,255 union all
select 247,'2012-04-25 20:48:58.537','2012-04-25 20:49:05.780',8,256 union all
select 248,'2012-04-25 20:49:10.067','2012-04-25 20:49:17.130',8,256 union all
select 249,'2012-04-25 20:49:34.420','2012-04-25 20:49:52.753',8,256 union all
select 250,'2012-04-25 20:50:17.840','2012-04-25 20:57:36.867',8,256 union all
select 251,'2012-04-25 21:32:30.710','2012-04-25 21:42:43.910',8,258 union all
select 252,'2012-04-25 22:35:38.130','2012-04-25 22:35:41.780',8,259 union all
select 253,'2012-04-25 22:35:42.643','2012-04-25 22:42:29.307',8,259 union all
select 254,'2012-04-26 09:37:57.020','2012-04-26 09:46:37.150',8,264 union all
select 323,'2012-04-26 09:46:37.073','2012-04-26 09:46:37.150',8,264 union all
select 337,'2012-04-26 10:22:18.027','2012-04-26 10:50:24.043',8,267 union all
select 338,'2012-04-26 16:53:30.607','2012-04-26 17:08:29.910',8,276 union all
select 339,'2012-04-26 16:53:46.743','2012-04-26 16:55:19.827',8,277 union all
select 340,'2012-04-26 16:55:19.970','2012-04-26 16:55:20.180',8,277 union all
select 341,'2012-04-26 16:55:20.317','2012-04-26 16:55:20.453',8,277 union all
select 342,'2012-04-26 16:55:20.517','2012-04-26 16:55:20.650',8,277 union all
select 343,'2012-04-26 16:55:20.797','2012-04-26 16:55:21.353',8,277--> 测试数据:[vAPVrsCIPBatchList]
if object_id('[vAPVrsCIPBatchList]') is not null drop table [vAPVrsCIPBatchList]
create table [vAPVrsCIPBatchList]([BatchID] int)
insert [vAPVrsCIPBatchList]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 union all
select 16 union all
select 17 union all
select 18 union all
select 19 union all
select 20 union all
select 21 union all
select 22 union all
select 23 union all
select 24 union all
select 25 union all
select 26 union all
select 27 union all
select 28 union all
select 29 union all
select 30 union all
select 31 union all
select 32 union all
select 33 union all
select 34 union all
select 35 union all
select 36 union all
select 37 union all
select 38 union all
select 39 union all
select 40 union all
select 41 union all
select 42 union all
select 43 union all
select 44 union all
select 45 union all
select 46 union all
select 47 union all
select 48 union all
select 49 union all
select 50 union all
select 51 union all
select 52 union all
select 53 union all
select 54 union all
select 55 union all
select 56 union all
select 57 union all
select 58 union all
select 59 union all
select 60 union all
select 61 union all
select 62 union all
select 63 union all
select 64 union all
select 65 union all
select 66 union all
select 67 union all
select 68 union all
select 69 union all
select 70 union all
select 71 union all
select 72 union all
select 73 union all
select 74 union all
select 75 union all
select 76 union all
select 77 union all
select 78 union all
select 79 union all
select 80 union all
select 81 union all
select 82 union all
select 83 union all
select 88 union all
select 89 union all
select 90 union all
select 91 union all
select 92 union all
select 93 union all
select 94 union all
select 95 union all
select 96 union all
select 97 union all
select 98 union all
select 99 union all
select 100 union all
select 101 union all
select 102 union all
select 103 union all
select 104 union all
select 105 union all
select 106 union all
select 107 union all
select 108 union all
select 109 union all
select 110 union all
select 111 union all
select 112 union all
select 113 union all
select 114 union all
select 115 union all
select 116 union all
select 117 union all
select 118 union all
select 119 union all
select 127 union all
select 131 union all
select 132 union all
select 133 union all
select 134 union all
select 135 union all
select 136 union all
select 137 union all
select 139 union all
select 140 union all
select 141 union all
select 142 union all
select 143 union all
select 144 union all
select 145 union all
select 146 union all
select 147 union all
select 148 union all
select 149 union all
select 150 union all
select 151 union all
select 152 union all
select 153 union all
select 159 union all
select 160 union all
select 161 union all
select 162 union all
select 163 union all
select 164 union all
select 165 union all
select 166 union all
select 167 union all
select 168 union all
select 169 union all
select 170 union all
select 171 union all
select 172 union all
select 173 union all
select 182 union all
select 183 union all
select 184 union all
select 185 union all
select 186 union all
select 187 union all
select 188 union all
select 189 union all
select 190 union all
select 191 union all
select 192 union all
select 193 union all
select 194 union all
select 195select b.BatchID,sum(isnull(a.total,0)) total from(
select ph_batchid,sum(DATEDIFF(s,ph_end,ph_start)) as total from [PhaseHold] a where exists(
select 1 from [vAPVrsCIPBatchList] b where a.ph_batchid=b.BatchID
) and a.ph_phaseid=8 or a.ph_phaseid=2 group by ph_batchid)a
right join [vAPVrsCIPBatchList] b
on a.ph_batchid=b.BatchID group by b.BatchID
/*
BatchID total
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
21 0
22 0
23 0
24 0
25 0
26 0
27 0
28 0
29 0
30 0
31 0
32 0
33 0
34 0
35 0
36 0
37 0
38 0
39 0
40 0
41 0
42 0
43 0
44 0
45 0
46 0
47 0
48 0
49 0
50 0
51 0
52 0
53 0
54 0
55 0
56 0
57 0
58 0
59 0
60 0
61 0
62 0
63 0
64 0
65 0
66 0
67 0
68 0
69 0
70 0
71 0
72 0
73 0
74 0
75 0
76 0
77 0
78 0
79 0
80 0
81 0
82 0
83 0
88 0
89 0
90 0
91 0
92 0
93 0
94 0
95 0
96 0
97 0
98 0
99 0
100 0
101 0
102 0
103 0
104 0
105 0
106 0
107 0
108 0
109 0
110 0
111 0
112 0
113 0
114 0
115 0
116 0
117 0
118 0
119 0
127 0
131 0
132 0
133 0
134 0
135 0
136 0
137 0
139 0
140 0
141 0
142 0
143 0
144 0
145 0
146 0
147 0
148 0
149 0
150 0
151 0
152 0
153 0
159 0
160 0
161 0
162 0
163 0
164 0
165 0
166 0
167 0
168 0
169 0
170 0
171 0
172 0
173 0
182 0
183 0
184 0
185 0
186 0
187 0
188 0
189 0
190 0
191 0
192 0
193 0
194 0
195 0
196 0
197 0
198 0
207 0
208 0
209 0
212 0
213 0
214 0
215 0
216 0
217 0
218 0
219 0
220 0
221 0
222 0
223 0
224 0
225 0
226 0
227 0
234 0
235 0
236 0
237 0
238 0
239 0
240 0
241 0
242 0
243 0
244 0
245 0
246 0
247 0
248 0
249 -216
250 -603
251 -483
252 -529
253 0
254 -515
255 -692
256 -471
257 0
258 -613
259 -410
262 0
264 -520
267 -1686
275 0
276 -899
277 -95
278 0
279 0
280 0
281 0
282 0
283 0
284 0
285 0
286 0
287 0
288 0
289 0
290 0
*/
请教sql语句怎么写啊?
select BatchID,
SUM(case when ph_phaseid=2 then DATEDIFF(s,ph_start,ph_end) else 0 end) [ph_phaseid=2],
SUM(case when ph_phaseid=8 then DATEDIFF(s,ph_start,ph_end) else 0 end) as[ph_phaseid=8]
from(
select a.BatchID,b.ph_start,b.ph_end,b.ph_phaseid from [vAPVrsCIPBatchList] a
left join [PhaseHold] b on a.BatchID=b.[ph_batchid])t
group by BatchID
/*
BatchID ph_phaseid=2 ph_phaseid=8
1 0 0
2 0 0
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0
8 0 0
9 0 0
10 0 0
11 0 0
12 0 0
13 0 0
14 0 0
15 0 0
16 0 0
17 0 0
18 0 0
19 0 0
20 0 0
21 0 0
22 0 0
23 0 0
24 0 0
25 0 0
26 0 0
27 0 0
28 0 0
29 0 0
30 0 0
31 0 0
32 0 0
33 0 0
34 0 0
35 0 0
36 0 0
37 0 0
38 0 0
39 0 0
40 0 0
41 0 0
42 0 0
43 0 0
44 0 0
45 0 0
46 0 0
47 0 0
48 0 0
49 0 0
50 0 0
51 0 0
52 0 0
53 0 0
54 0 0
55 0 0
56 0 0
57 0 0
58 0 0
59 0 0
60 0 0
61 0 0
62 0 0
63 0 0
64 0 0
65 0 0
66 0 0
67 0 0
68 0 0
69 0 0
70 0 0
71 0 0
72 0 0
73 0 0
74 0 0
75 0 0
76 0 0
77 0 0
78 0 0
79 0 0
80 0 0
81 0 0
82 0 0
83 0 0
88 0 0
89 0 0
90 0 0
91 0 0
92 0 0
93 0 0
94 0 0
95 0 0
96 0 0
97 0 0
98 0 0
99 0 0
100 0 0
101 0 0
102 0 0
103 0 0
104 0 0
105 0 0
106 0 0
107 0 0
108 0 0
109 0 0
110 0 0
111 0 0
112 0 0
113 0 0
114 0 0
115 0 0
116 0 0
117 0 0
118 0 0
119 0 0
127 0 0
131 0 0
132 0 0
133 0 0
134 0 0
135 0 0
136 0 0
137 0 0
139 0 0
140 0 0
141 0 0
142 0 0
143 0 0
144 0 0
145 0 0
146 0 0
147 0 0
148 0 0
149 0 0
150 0 0
151 0 0
152 0 0
153 0 0
159 0 0
160 0 0
161 0 0
162 0 0
163 0 0
164 0 0
165 0 0
166 0 0
167 0 0
168 0 0
169 0 0
170 0 0
171 0 0
172 0 0
173 0 0
182 0 0
183 0 0
184 0 0
185 0 0
186 0 0
187 0 0
188 0 0
189 0 0
190 0 0
191 0 0
192 0 0
193 0 0
194 0 0
195 0 0
196 0 0
197 0 0
198 0 0
207 0 0
208 0 0
209 0 0
212 0 0
213 0 0
214 0 0
215 0 0
216 0 0
217 0 0
218 0 0
219 0 0
220 0 0
221 0 0
222 0 0
223 0 0
224 0 0
225 0 0
226 0 0
227 0 0
234 0 0
235 0 0
236 0 0
237 0 0
238 0 0
239 0 0
240 0 0
241 0 0
242 0 0
243 0 0
244 0 0
245 0 0
246 0 0
247 0 0
248 0 0
249 0 216
250 198 405
251 0 483
252 0 529
253 0 0
254 0 515
255 0 692
256 0 471
257 0 0
258 0 613
259 0 410
262 0 0
264 0 520
267 0 1686
275 0 0
276 0 899
277 0 95
278 0 0
279 0 0
280 0 0
281 0 0
282 0 0
283 0 0
284 0 0
285 0 0
286 0 0
287 0 0
288 0 0
289 0 0
290 0 0
*/