如何合并同表统计查询以提升SQL执行效率？

你可以通过单次扫描person表的方式得到相同结果，避免两次全表查询的重复开销，具体方案分两种情况：

支持UNPIVOT的数据库（如SQL Server、Oracle） #

SELECT attr_name, count_id, count_missing, count_total
FROM (
    -- 单次扫描表，计算所有所需统计值
    SELECT
        COUNT(id) AS count_id,
        COUNT(firstname) AS count_firstname,
        COUNT(lastname) AS count_lastname
    FROM person
) AS stats
-- 将统计值拆分为多行，对应不同属性
UNPIVOT (
    count_total FOR attr_name IN (count_firstname AS 'firstname', count_lastname AS 'lastname')
) AS unpvt
-- 计算缺失值
CROSS APPLY (
    SELECT count_id - count_total AS count_missing
) AS missing_calc;

不支持UNPIVOT的数据库（如MySQL、PostgreSQL） #

用UNION ALL结合单次统计结果拆分：

WITH stats AS (
    -- 仅一次全表扫描，获取所有统计数据
    SELECT
        COUNT(id) AS count_id,
        COUNT(firstname) AS count_firstname,
        COUNT(lastname) AS count_lastname
    FROM person
)
SELECT
    'firstname' AS attr_name,
    count_id,
    count_id - count_firstname AS count_missing,
    count_firstname AS count_total
FROM stats
UNION ALL
SELECT
    'lastname' AS attr_name,
    count_id,
    count_id - count_lastname AS count_missing,
    count_lastname AS count_total
FROM stats;

优化说明 #

原查询两次独立扫描person表，每次耗时约5秒，总耗时叠加为10秒。优化后的查询仅需一次全表扫描，计算出所有统计值后再拆分生成目标结果，总耗时会接近单次扫描的5秒，大幅降低执行时间。另外，用UNION ALL替代原查询的UNION——因为这里不存在重复行，UNION会额外执行去重操作，增加不必要的性能开销。

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