SUM 幾個 CASE 結果

鑑於：

CASE 
    WHEN A IS NULL THEN 1 ELSE 0
END AS [A]
, CASE
    WHEN B IS NULL THEN 1 ELSE 0
END AS [B]
, CASE
    WHEN C IS NULL THEN 1 ELSE 0 
END AS [C]

---

我如何將 A+B+C 求和為 3 種不同 CASE WHEN 的結果

您沒有指定您的 RDBMS（SQL Server、Oracle 等），但如果它支持Common Table Expressions，您可以將主要選擇邏輯封裝在 a 中CTE並從中選擇/求和。這是使用 SQL Server 的解決方案。

Declare @T table (A int, B int, C int)
insert into @T(A,B,C) values(null,2,null)

;WITH _cte
AS (
   SELECT CASE 
           WHEN A IS NULL
               THEN 1
           ELSE 0
           END AS [A]
       ,CASE 
           WHEN B IS NULL
               THEN 1
           ELSE 0
           END AS [B]
       ,CASE 
           WHEN C IS NULL
               THEN 1
           ELSE 0
           END AS [C]
   FROM @T
   )
SELECT A + B + C as Results
FROM _cte

---

Results
2

---

正如評論指出的那樣，您不需要公用表表達式。派生表也可以達到目的

Declare @T table (A int, B int, C int)
insert into @T(A,B,C) values(null,2,null)
SELECT A + B + C AS Results
FROM (
   SELECT CASE 
           WHEN A IS NULL
               THEN 1
           ELSE 0
           END AS [A]
       ,CASE 
           WHEN B IS NULL
               THEN 1
           ELSE 0
           END AS [B]
       ,CASE 
           WHEN C IS NULL
               THEN 1
           ELSE 0
           END AS [C]
   FROM @T
   ) a

這個如何？請分享您對此的看法。就性能而言，此解決方案與使用 CTE 有什麼區別？

select sum(CASE
            WHEN A IS NULL THEN 1 ELSE 0 END) 
       + 
      sum(CASE
            WHEN B IS NULL THEN 1 ELSE 0 END) 
       + 
      sum(CASE
            WHEN C IS NULL THEN 1 ELSE 0 END) sum_result
 from <Your_Table>;

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