從查找表中獲取兩列的總和時出現 NULL 值

我正在使用 SQL Server 並從兩個查找表中獲取數據，並希望獲取這兩列的總和。

當這兩列（查找值）的總和給出NULL值時，就會出現問題。但是，我習慣COALESCE將NULL值更改為零。

我計算總和的行是這一行：(t2.[Score]+t3.[Score]) as Total

select  t1.[ID]
     ,t1.[District]
     ,[CLLG_Interventiond]
     ,t1.[UC_HH]
     ,t1.[Benef_Household]
     ,t1.[Duration_Years]
     ,t1.[Duration_Months]
     ,t1.[Budget_PKR]
     ,t1.[Priority]
     ,COALESCE(t2.[Score],0) as E_Score
     ,COALESCE(t3.[Score],0) as UCDP_Score
     ,(t2.[Score]+t3.[Score]) as Total
 FROM [dbo].[Data] as t1
 left outer join E_Growth as t2
 on t1.CLLG_Interventiond = t2.Intervention
 left outer join UCDP as t3
on  t1.Priority = t3.Priority

嘗試使用像這樣COALESCE的值SUM ：

COALESCE(t2.[Score],0) + COALESCE(t3.[Score],0) as Total

查詢將如下所示：

select  t1.[ID]
     ,t1.[District]
     ,[CLLG_Interventiond]
     ,t1.[UC_HH]
     ,t1.[Benef_Household]
     ,t1.[Duration_Years]
     ,t1.[Duration_Months]
     ,t1.[Budget_PKR]
     ,t1.[Priority]
     ,COALESCE(t2.[Score],0) as E_Score
     ,COALESCE(t3.[Score],0) as UCDP_Score
     ,COALESCE(t2.[Score],0) + COALESCE(t3.[Score],0) as Total
 FROM [dbo].[Data] as t1
 left outer join E_Growth as t2
 on t1.CLLG_Interventiond = t2.Intervention
 left outer join UCDP as t3
on  t1.Priority = t3.Priority

由於您已經處理了該問題，因此您可以嵌套查詢並在外層計算總數：

SELECT T.*, T.E_SCORE + T.UCDP_SCORE AS total
FROM (
   select  t1.[ID]
          ,t1.[District]
          ,[CLLG_Interventiond]
          ,t1.[UC_HH]
          ,t1.[Benef_Household]
          ,t1.[Duration_Years]
          ,t1.[Duration_Months]
          ,t1.[Budget_PKR]
          ,t1.[Priority]
          ,COALESCE(t2.[Score],0) as E_Score
          ,COALESCE(t3.[Score],0) as UCDP_Score

  FROM [dbo].[Data] as t1
  left outer join E_Growth as t2
     on t1.CLLG_Interventiond = t2.Intervention
  left outer join UCDP as t3
     on  t1.Priority = t3.Priority
) as T

可以以相同的方式使用 CTE

引用自：https://dba.stackexchange.com/questions/315304