我有一个存储过程如下：
CREATE PROC [dbo].[Incidents]
(@SiteName varchar(200))
AS
SELECT
(
SELECT SUM(i.Logged)
FROM tbl_Sites s
INNER JOIN tbl_Incidents i
ON s.Location = i.Location
WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm,DATEDIFF(mm,GetDate()) -1,0)
GROUP BY s.Sites
) AS LoggedIncidents
'tbl_Sites contains a list of reported on sites.
'tbl_Incidents containts a generated list of total incidents by site/date (monthly)
'If a site doesnt have any incidents that month it wont be listed.
我遇到的问题是，一个网站本月没有任何事件，因此，当我运行此sproc时，我会获得该网站返回的NULL值，但是我需要返回一个零/ 0以在图表中使用在SSRS。
我尝试过使用合并并且无效。
SELECT COALESCE(SUM(c.Logged,0))
SELECT SUM(ISNULL(c.Logged,0))
有没有办法得到这样格式正确？
干杯，
背风处
解决方法
把它放在外面
SELECT COALESCE(
(
SELECT SUM(i.Logged)
FROM tbl_Sites s
INNER JOIN tbl_Incidents i
ON s.Location = i.Location
WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm,0)
GROUP BY s.Sites
),0) AS LoggedIncidents
如果您返回多行，请将INNER JOIN更改为LEFT JOIN
SELECT COALESCE(SUM(i.Logged),0) FROM tbl_Sites s LEFT JOIN tbl_Incidents i ON s.Location = i.Location WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm,0) GROUP BY s.Sites
顺便说一句，如果没有保证，不要在集合函数内部放置任何函数或表达式。不要把ISNULL，COALESCE放在SUM里面，使用函数/表达式在内部聚集跛行性能，查询将执行表扫描
