|
我有一个存储过程,如下所示:! h+ D, y' Y' ~2 b. y8 M
CREATE PROC [dbo].[Incidents]3 @9 f! V6 c9 Y& T$ }- g
(@SiteName varchar(200))
S2 Y; t% J) p3 uAS! k7 |+ s9 H6 J1 O
SELECT# Z* |0 k: r" X4 H- w- v
(
% n$ U& {. A, R E3 r SELECT SUM(i.Logged)
3 ~' g. V& e% y FROM tbl_Sites s + D! y8 F0 G+ y- ]. \, m; C. z
INNER JOIN tbl_Incidents i
) n2 Y6 b3 ?$ \7 f* l* C4 ]- `5 @6 Z ON s.Location = i.Location
4 V0 N6 m$ e( U, K WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0) ( v, }2 M# l$ l. t& r3 y- F& V
GROUP BY s.Sites
3 E" N( I8 G1 y; w3 P+ Y. Q! N; Y) AS LoggedIncidents
) a# K8 u1 `/ ~+ [: p! ^5 g'tbl_Sites contains a list of reported on sites./ h- P7 D v; a W. @* Z/ P4 G, `
'tbl_Incidents contains a generated list of total incidents by site/date (monthly)
$ V6 Z" k y) z Q' W'If a site doesn't have any incidents that month it wont be listed.
' B1 J1 g, K2 H& [/ v8 f) Y }我遇到的问题是,该站点本月没有任何事件,因此当我运行此proc时,我为该站点返回了NULL值,但是我需要返回零/ 0以便在内部使用SSRS中的图表。
5 v! B0 t8 I+ ?! D0 v$ H$ ?我曾尝试使用合并和isull无济于事。; |6 }/ U4 A9 c. d/ }5 X
SELECT COALESCE(SUM(c.Logged,0))
c& ~* U4 _2 z' I SELECT SUM(ISNULL(c.Logged,0))
( a( W, ], x. @7 C2 @8 H8 {0 e有没有一种方法可以正确格式化此格式?
/ ~9 j8 [' l( F3 G; U( V F* B干杯,8 K' v6 z/ P3 |' k7 ~) r
背风处
6 H2 R# K- r! {& @0 m
2 C$ Y) W9 D4 z, i6 ~/ {& S解决方案:
7 J3 p6 u% T% `" ~
# u7 D3 b. p2 d" H" i
" n6 U( U! k' r5 I0 I' u6 A5 F I" r8 G) W B
放在外面:
0 {; Y, o* [6 l' f# h4 W5 ySELECT COALESCE(
. R# t, h7 o3 L# o9 `& I(
' N* M6 H2 i1 n' ` SELECT SUM(i.Logged)
# Y& P7 |5 b4 L L7 u( }9 ]' t FROM tbl_Sites s
) N( }9 Y6 M4 G) ]/ h5 ~$ ]) H! k INNER JOIN tbl_Incidents i
2 d6 E" g! Y2 M e ON s.Location = i.Locatio餃﹏ 7 E6 B g4 B. j5 K2 j6 G7 d2 _
WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0) , F" L6 U% I# w) u! ]
GROUP BY s.Sites
! r7 G6 }" b) P" K), 0) AS LoggedIncidents
- v5 `- C* Q) r9 K- ?如果要返回多行,请将INNER JOIN更改为 LEFT JOIN* ~1 k( |- H3 I( G
SELECT COALESCE(SUM(i.Logged),0)( C E7 T) b9 @! W- u V
FROM tbl_Sites s
9 j3 @8 f" q/ `/ s: b: f7 A2 |LEFT JOIN tbl_Incidents i
* r: ?8 `- K |5 jON s.Location = i.Locatio餃﹏ - p/ C- B! L/ B8 f5 v
WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0)
: j( X: a; r3 F- v n* h' x2 LGROUP BY s.Sites
' q% F) g1 z7 S. f( f0 l- y3 Q顺便说一句,如果没有保证,请勿在聚合函数中放置任何函数或表达式,例如,不要在ISUM中放置ISNULL,COALESCE,在聚合内部使用函数/表达式削弱性能,查询将通过表扫描执行 |
|