|
我有一个存储过程,如下所示:
- G0 [1 a- ^9 ~( l0 A" Q/ g* [6 ]CREATE PROC [dbo].[Incidents] n' V. `1 \+ c `: j4 \6 D
(@SiteName varchar(200))
+ E' k' T- G5 BAS1 Z& K( i6 v3 t) i! i& N/ v3 n
SELECT: g$ Y7 `2 P* P1 E' m: k% F% b4 f+ E8 c; m
( ) v% @/ n) d& _8 U( J( ]" L
SELECT SUM(i.Logged)
% c+ z8 J) H; q+ g M2 G2 A) J FROM tbl_Sites s : b1 d3 @% p: R, }
INNER JOIN tbl_Incidents i
" G) k3 k" A+ P ON s.Location = i.Location
6 K( s# z8 l8 `" u1 I( w WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0) 4 Z2 d6 ?- P" l% ?
GROUP BY s.Sites 0 `- Q @# Y8 F z1 `
) AS LoggedIncidents
' J8 a4 z( ]5 z9 w'tbl_Sites contains a list of reported on sites.- n& _6 s, r, E9 ~8 C
'tbl_Incidents contains a generated list of total incidents by site/date (monthly)" l# o- X1 @3 f3 d0 u4 |( O2 D/ s
'If a site doesn't have any incidents that month it wont be listed.
: o* T( K7 j' q/ V5 v我遇到的问题是,该站点本月没有任何事件,因此当我运行此proc时,我为该站点返回了NULL值,但是我需要返回零/ 0以便在内部使用SSRS中的图表。
/ Z& y) u' v' K* ^2 i* J# I我曾尝试使用合并和isull无济于事。! P, G+ f$ c1 I% D# a3 Z
SELECT COALESCE(SUM(c.Logged,0))
, ?$ C, f: c4 i2 Q SELECT SUM(ISNULL(c.Logged,0)) ?5 \5 {7 C4 N/ b+ b/ X- }
有没有一种方法可以正确格式化此格式?
: w$ x4 q& l! `4 U+ \干杯,
& Q$ [/ c4 }+ S/ n! m背风处' T% \1 t B9 i
- t$ l$ _+ f( v+ Y! i
解决方案:7 H% B9 ?( i- E; `7 s. K
0 E7 \8 i8 X8 N, C5 p
; c4 e& _, k" z. X C9 {
; c2 t4 h2 y( J* T 放在外面:
+ o6 E& ?, B" nSELECT COALESCE(
* Q* ~" a# g; w9 c' N/ \(
' H. y5 G3 z: j7 n/ K0 N$ c! _ SELECT SUM(i.Logged) 3 S5 x: f9 A- U# a2 @1 _* n
FROM tbl_Sites s
* W( c4 D3 q& ^( N. D INNER JOIN tbl_Incidents i
5 o) O3 G- Y2 b/ q4 ] ON s.Location = i.Locatio餃﹏
. Y: H+ b; y6 \ WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0) / u: _- d' h$ t+ c
GROUP BY s.Sites ( g) s+ D4 k2 X1 }
), 0) AS LoggedIncidents
) ^5 K4 o: i9 p6 a! a如果要返回多行,请将INNER JOIN更改为 LEFT JOIN5 H5 ^) f# H% {5 d h1 J
SELECT COALESCE(SUM(i.Logged),0), B; `8 J4 u! B8 R# a2 r
FROM tbl_Sites s ! g* O3 Y% z/ I; I6 Q
LEFT JOIN tbl_Incidents i
5 @/ X1 F1 h$ k3 y JON s.Location = i.Locatio餃﹏ / y( V- s b# }1 V% }9 q8 \3 M
WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0)
9 t, C. I- z, _- T$ m* kGROUP BY s.Sites
( Y; e$ x! Q. B顺便说一句,如果没有保证,请勿在聚合函数中放置任何函数或表达式,例如,不要在ISUM中放置ISNULL,COALESCE,在聚合内部使用函数/表达式削弱性能,查询将通过表扫描执行 |
|