處理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中文網其他相關文章！
