添加三个整数变量将结果显示为 Null，为什么

Question

这是我尝试过的代码

SELECT 
 case when InsertedRows is null then 0 else InsertedRows end InsertedRows
,case when FailedRows is null then 0 else FailedRows end FailedRows
,case when UpdatedRows is null then 0 else UpdatedRows end UpdatedRows
,InsertedRows + UpdatedRows + FailedRows as tot
FROM PATS.ImportLog
WHERE CreatedBy='suvaneeth' 
      AND ISNULL(CompletedYN,0) = 0 
      AND CAST(CreatedDate AS date) >= CAST(GETDATE() AS date)

我得到的结果tot是空的

99  0   0   NULL

我期待结果是 99

Answer 1

select 
case when InsertedRows is null then 0 else InsertedRows end InsertedRows
,case when FailedRows is null then 0 else FailedRows end FailedRows
,case when UpdatedRows is null then 0 else UpdatedRows end UpdatedRows
,COALESCE(InsertedRows,0) + COALESCE(UpdatedRows,0) + COALESCE(FailedRows,0) as tot
from PATS.ImportLog
WHERE CreatedBy='suvaneeth' AND ISNULL(CompletedYN,0)=0 AND CAST(CreatedDate AS date)>=CAST(GETDATE() AS date)

Answer 2

您可以尝试使用 ISNULL(insertedrows, 0) 或 COALESCE(insertedrows, 0) 而不是 CASE

Answer 3

尝试这种方式。

select 
  Coalesce(InsertedRows, 0) InsertedRows
  ,Coalesce(FailedRows, 0) FailedRows
  ,Coalesce(UpdatedRows, 0) UpdatedRows
  ,Coalesce(InsertedRows, 0) + Coalesce(FailedRows, 0)+ Coalesce(UpdatedRows, 0) as tot
from PATS.ImportLog
WHERE CreatedBy='suvaneeth' AND ISNULL(CompletedYN,0)=0 AND CAST(CreatedDate AS date)>=CAST(GETDATE() AS date)