SQL 單一查詢同時統計總筆數與各狀態筆數：兩種高效寫法

需求

表 TB 有欄位 STATUS (1=正確, 0=錯誤)，單一查詢取得：總筆數、正確筆數、錯誤筆數。

方法一：UNION ALL + 彙總 (傳統相容寫法)

SELECT SUM(正確數) + SUM(錯誤數) AS 總記錄數,
       SUM(正確數) AS 正確數,
       SUM(錯誤數) AS 錯誤數
FROM (
    SELECT COUNT(1) AS 正確數, 0 AS 錯誤數
    FROM TB WHERE STATUS = 1
    UNION ALL
    SELECT 0 AS 正確數, COUNT(1) AS 錯誤數
    FROM TB WHERE STATUS = 0
) a;

• 掃描表兩次 (各自 WHERE 過濾)。
• 相容所有 SQL 版本。

方法二：CASE WHEN 條件彙總 (推薦，單次掃描)

SELECT COUNT(1) AS 總記錄數,
       SUM(CASE WHEN STATUS = 1 THEN 1 ELSE 0 END) AS 正確數,
       SUM(CASE WHEN STATUS = 0 THEN 1 ELSE 0 END) AS 錯誤數
FROM TB;

• 單次全表掃描，效能顯著優於方法一。
• 可擴充多狀態：SUM(CASE WHEN STATUS = 2 THEN 1 ELSE 0 END) AS 待審數。
• SQL Server 2012+ 可用 IIF(STATUS=1,1,0) 簡化語法。

效能對比 (百萬筆測試)

進階：搭配 GROUP BY 多分群統計

SELECT Category,
       COUNT(1) AS 總數,
       SUM(CASE WHEN STATUS = 1 THEN 1 ELSE 0 END) AS 正確,
       SUM(CASE WHEN STATUS = 0 THEN 1 ELSE 0 END) AS 錯誤
FROM TB
GROUP BY Category;

結論

優先使用 CASE WHEN 條件彙總，單次掃描、語法簡潔、擴充性佳。僅在極舊版本不支援 CASE 時才考慮 UNION ALL 寫法。