处理 SQL Server 查询中的 NULL 值：用 0 替换
使用 SQL Server 数据库时，在查询结果中遇到 NULL 值可能会导致以下情况：阻碍数据分析。例如，考虑以下查询：
Select c.rundate,
sum(case when c.runstatus = 'Succeeded' then 1 end) as Succeeded,
sum(case when c.runstatus = 'Failed' then 1 end) as Failed,
sum(case when c.runstatus = 'Cancelled' then 1 end) as Cancelled,
count(*) as Totalrun
from
(Select a.name,
case when b.run_status = 0 Then 'Failed'
when b.run_status = 1 Then 'Succeeded'
when b.run_status = 2 Then 'Retry'
Else 'Cancelled'
End as Runstatus,
cast(substring(convert(varchar(8), run_date), 1, 4) + '/' + substring(convert(varchar(8), run_date), 5, 2) + '/' + substring(convert(varchar(8), run_date), 7, 2) as Datetime) as RunDate
from msdb.dbo.sysjobs as a(nolock) inner join msdb.dbo.sysjobhistory as b(nolock)
on a.job_id = b.job_id
where a.name = 'AI'
and b.step_id = 0) as c
group by
c.rundate此查询从两个表（sysjobs 和 sysjobhistory）检索数据，并根据 runstatus 列执行计算。但是，如果 runstatus 具有 NULL 值，查询可能会返回意外结果。
将 NULL 替换为 0
要在 SQL Server 中将 NULL 值替换为 0，请使用 isnull （） 功能。如果输入值为NULL，该函数返回指定值；否则，它返回输入值。
isnull(myColumn, 0)
在给定的查询中，c.runstatus 可能具有 NULL 值。要将这些值替换为 0，请修改查询如下：
Select c.rundate,
sum(case when isnull(c.runstatus, 0) = 'Succeeded' then 1 end) as Succeeded,
sum(case when isnull(c.runstatus, 0) = 'Failed' then 1 end) as Failed,
sum(case when isnull(c.runstatus, 0) = 'Cancelled' then 1 end) as Cancelled,
count(*) as Totalrun
from
(Select a.name,
case when b.run_status = 0 Then 'Failed'
when b.run_status = 1 Then 'Succeeded'
when b.run_status = 2 Then 'Retry'
Else 'Cancelled'
End as Runstatus,
cast(substring(convert(varchar(8), run_date), 1, 4) + '/' + substring(convert(varchar(8), run_date), 5, 2) + '/' + substring(convert(varchar(8), run_date), 7, 2) as Datetime) as RunDate
from msdb.dbo.sysjobs as a(nolock) inner join msdb.dbo.sysjobhistory as b(nolock)
on a.job_id = b.job_id
where a.name = 'AI'
and b.step_id = 0) as c
group by
c.rundate通过使用 isnull()，在执行计算之前将 c.runstatus 中的 NULL 值替换为 0。这可以确保即使存在缺失数据，查询也能返回准确的结果。
以上是如何处理 SQL Server 查询中的 NULL 值并将其替换为 0？的详细内容。更多信息请关注PHP中文网其他相关文章！
