sql – 更改SUM将NULL返回为零

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.Locatio?n  
    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.Locatio?n  
WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm,0)  
GROUP BY s.Sites

顺便说一句，如果没有保证，不要在集合函数内部放置任何函数或表达式。不要把ISNULL，COALESCE放在SUM里面，使用函数/表达式在内部聚集跛行性能，查询将执行表扫描

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!