SQL使用UNION ALL做空值校验时如何仅查一次表总行数填充所有行

问题修正方案 #

你现有代码的核心问题有两个：

1. LEFT JOIN仅关联了最后一段SELECT查询，前面两段UNION的结果无法获取到TOTAL_COUNT字段
2. 总计数统计的WHERE条件错误，你要统计的是符合DateField过滤条件的所有行数，不需要加列空的过滤规则

推荐使用CTE（公共表表达式）实现，可读性更高且仅会扫描一次表统计总计数，符合你降低服务器压力的需求：

WITH total_stat AS (
    -- 仅查询一次符合DateField条件的总行数
    SELECT 
        CAST('DB_NAME_HERE' AS VARCHAR(255)) AS DB_NAME,
        COUNT(*) AS TOTAL_COUNT
    FROM [Name of Db]
    WHERE DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
),
defect_stats AS (
    SELECT
        CAST('RULE_ID_1' AS VARCHAR(255)) AS RULE_ID,
        CAST('DB_NAME_HERE' AS VARCHAR(255)) AS DB_NAME,
        CAST('1STCOLUMN' AS VARCHAR(255)) AS COLUMN_NAME,
        COUNT(1) AS DEFECT_COUNT
    FROM [Name of Db]
    WHERE [Name of column 1] IS NULL
      AND DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
    UNION ALL
    SELECT
        CAST('RULE_ID_2' AS VARCHAR(255)) AS RULE_ID,
        CAST('DB_NAME_HERE' AS VARCHAR(255)) AS DB_NAME,
        CAST('2NDCOLUMN' AS VARCHAR(255)) AS COLUMN_NAME,
        COUNT(1) AS DEFECT_COUNT
    FROM [Name of Db]
    WHERE [Name of column 2] IS NULL
      AND DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
    UNION ALL
    SELECT
        CAST('RULE_ID_3' AS VARCHAR(255)) AS RULE_ID,
        CAST('DB_NAME_HERE' AS VARCHAR(255)) AS DB_NAME,
        CAST('3RDCOLUMN' AS VARCHAR(255)) AS COLUMN_NAME,
        COUNT(1) AS DEFECT_COUNT
    FROM [Name of Db]
    WHERE [Name of column 3] IS NULL
      AND DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
)
-- 关联结果集输出最终数据
SELECT 
    d.RULE_ID,
    d.DB_NAME,
    d.COLUMN_NAME,
    d.DEFECT_COUNT AS `DEFECT_COUNT(Nulls)`,
    t.TOTAL_COUNT AS `TOTAL_COUNT(#Rows)`
FROM defect_stats d
LEFT JOIN total_stat t ON d.DB_NAME = t.DB_NAME

如果你需要更简洁的写法，也可以直接在每个SELECT子句中加入总计数子查询，主流数据库的优化器会自动识别重复子查询，仅执行一次总计数统计，不会重复扫描表：

SELECT
    CAST('RULE_ID_1' AS VARCHAR(255)) AS RULE_ID,
    CAST('DB_NAME_HERE' AS VARCHAR(255)) AS DB_NAME,
    CAST('1STCOLUMN' AS VARCHAR(255)) AS COLUMN_NAME,
    COUNT(1) AS `DEFECT_COUNT(Nulls)`,
    (SELECT COUNT(*) FROM [Name of Db] WHERE DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))) AS `TOTAL_COUNT(#Rows)`
FROM [Name of Db]
WHERE [Name of column 1] IS NULL
  AND DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
UNION ALL
SELECT
    CAST('RULE_ID_2' AS VARCHAR(255)) AS RULE_ID,
    CAST('DB_NAME_HERE' AS VARCHAR(255)) AS DB_NAME,
    CAST('2NDCOLUMN' AS VARCHAR(255)) AS COLUMN_NAME,
    COUNT(1) AS `DEFECT_COUNT(Nulls)`,
    (SELECT COUNT(*) FROM [Name of Db] WHERE DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))) AS `TOTAL_COUNT(#Rows)`
FROM [Name of Db]
WHERE [Name of column 2] IS NULL
  AND DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
UNION ALL
SELECT
    CAST('RULE_ID_3' AS VARCHAR(255)) AS RULE_ID,
    CAST('DB_NAME_HERE' AS VARCHAR(255)) AS DB_NAME,
    CAST('3RDCOLUMN' AS VARCHAR(255)) AS COLUMN_NAME,
    COUNT(1) AS `DEFECT_COUNT(Nulls)`,
    (SELECT COUNT(*) FROM [Name of Db] WHERE DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))) AS `TOTAL_COUNT(#Rows)`
FROM [Name of Db]
WHERE [Name of column 3] IS NULL
  AND DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))

补充说明

• 原代码中每个CAST表达式多了一个多余的右括号，已在修正代码中移除
• 两种实现方式输出的结果结构完全匹配你要求的格式，所有行的TOTAL_COUNT值一致

内容的提问来源于stack exchange，提问作者Supernova