如何在现有MySQL UNION查询中添加上月与当月计数差值行？

首先，你的原查询用了多个独立子查询来统计每个nationality的数量，其实可以用条件聚合来简化，这样不仅代码更简洁，执行效率也会更高。接下来我们一步步实现添加差值行的需求：

第一步：重构当月和上月的统计查询 #

先把当月和上月的统计合并成更高效的写法，同时用UNION ALL替代UNION（避免不必要的去重开销）：

-- 当月统计
SELECT
    COUNT(CASE WHEN nationality_id = 23 THEN 1 END) AS type1,
    COUNT(CASE WHEN nationality_id = 24 THEN 1 END) AS type2,
    COUNT(CASE WHEN nationality_id = 25 THEN 1 END) AS type3,
    COUNT(CASE WHEN nationality_id = 26 THEN 1 END) AS type4
FROM table_x
WHERE YEAR(START_DATE) = YEAR(NOW()) AND MONTH(START_DATE) = MONTH(NOW())

UNION ALL

-- 上月统计
SELECT
    COUNT(CASE WHEN nationality_id = 23 THEN 1 END) AS type1,
    COUNT(CASE WHEN nationality_id = 24 THEN 1 END) AS type2,
    COUNT(CASE WHEN nationality_id = 25 THEN 1 END) AS type3,
    COUNT(CASE WHEN nationality_id = 26 THEN 1 END) AS type4
FROM table_x
WHERE YEAR(START_DATE) = YEAR(NOW() - INTERVAL 1 MONTH) AND MONTH(START_DATE) = MONTH(NOW() - INTERVAL 1 MONTH)

这里额外加上了年份判断，避免跨年度的统计错误（比如1月统计上月时，不会把今年12月的数据算进去）。

第二步：添加差值行 #

要计算上月与当月的差值，我们可以把前两行的结果存入临时表，再计算差值并追加到结果中。如果你的MySQL版本是8.0及以上，推荐用CTE（WITH语句）来实现：

WITH monthly_stats AS (
    -- 当月统计，添加period字段方便区分行
    SELECT
        '当月' AS period,
        COUNT(CASE WHEN nationality_id = 23 THEN 1 END) AS type1,
        COUNT(CASE WHEN nationality_id = 24 THEN 1 END) AS type2,
        COUNT(CASE WHEN nationality_id = 25 THEN 1 END) AS type3,
        COUNT(CASE WHEN nationality_id = 26 THEN 1 END) AS type4
    FROM table_x
    WHERE YEAR(START_DATE) = YEAR(NOW()) AND MONTH(START_DATE) = MONTH(NOW())

    UNION ALL

    -- 上月统计
    SELECT
        '上月' AS period,
        COUNT(CASE WHEN nationality_id = 23 THEN 1 END) AS type1,
        COUNT(CASE WHEN nationality_id = 24 THEN 1 END) AS type2,
        COUNT(CASE WHEN nationality_id = 25 THEN 1 END) AS type3,
        COUNT(CASE WHEN nationality_id = 26 THEN 1 END) AS type4
    FROM table_x
    WHERE YEAR(START_DATE) = YEAR(NOW() - INTERVAL 1 MONTH) AND MONTH(START_DATE) = MONTH(NOW() - INTERVAL 1 MONTH)
)
-- 取出前两行，再追加差值行
SELECT period, type1, type2, type3, type4 FROM monthly_stats
UNION ALL
SELECT
    '差值(上月-当月)' AS period,
    (SELECT type1 FROM monthly_stats WHERE period = '上月') - (SELECT type1 FROM monthly_stats WHERE period = '当月') AS type1,
    (SELECT type2 FROM monthly_stats WHERE period = '上月') - (SELECT type2 FROM monthly_stats WHERE period = '当月') AS type2,
    (SELECT type3 FROM monthly_stats WHERE period = '上月') - (SELECT type3 FROM monthly_stats WHERE period = '当月') AS type3,
    (SELECT type4 FROM monthly_stats WHERE period = '上月') - (SELECT type4 FROM monthly_stats WHERE period = '当月') AS type4;

如果你的MySQL版本不支持CTE，可以用嵌套子查询替代：

-- 用子查询代替CTE的写法
SELECT period, type1, type2, type3, type4 FROM (
    SELECT
        '当月' AS period,
        COUNT(CASE WHEN nationality_id = 23 THEN 1 END) AS type1,
        COUNT(CASE WHEN nationality_id = 24 THEN 1 END) AS type2,
        COUNT(CASE WHEN nationality_id = 25 THEN 1 END) AS type3,
        COUNT(CASE WHEN nationality_id = 26 THEN 1 END) AS type4
    FROM table_x
    WHERE YEAR(START_DATE) = YEAR(NOW()) AND MONTH(START_DATE) = MONTH(NOW())

    UNION ALL

    SELECT
        '上月' AS period,
        COUNT(CASE WHEN nationality_id = 23 THEN 1 END) AS type1,
        COUNT(CASE WHEN nationality_id = 24 THEN 1 END) AS type2,
        COUNT(CASE WHEN nationality_id = 25 THEN 1 END) AS type3,
        COUNT(CASE WHEN nationality_id = 26 THEN 1 END) AS type4
    FROM table_x
    WHERE YEAR(START_DATE) = YEAR(NOW() - INTERVAL 1 MONTH) AND MONTH(START_DATE) = MONTH(NOW() - INTERVAL 1 MONTH)
) AS monthly_stats

UNION ALL

SELECT
    '差值(上月-当月)' AS period,
    (SELECT type1 FROM (
        SELECT COUNT(CASE WHEN nationality_id = 23 THEN 1 END) AS type1 FROM table_x WHERE YEAR(START_DATE) = YEAR(NOW() - INTERVAL 1 MONTH) AND MONTH(START_DATE) = MONTH(NOW() - INTERVAL 1 MONTH)
    ) AS last_month) - (SELECT type1 FROM (
        SELECT COUNT(CASE WHEN nationality_id = 23 THEN 1 END) AS type1 FROM table_x WHERE YEAR(START_DATE) = YEAR(NOW()) AND MONTH(START_DATE) = MONTH(NOW())
    ) AS current_month) AS type1,
    (SELECT type2 FROM (
        SELECT COUNT(CASE WHEN nationality_id = 24 THEN 1 END) AS type2 FROM table_x WHERE YEAR(START_DATE) = YEAR(NOW() - INTERVAL 1 MONTH) AND MONTH(START_DATE) = MONTH(NOW() - INTERVAL 1 MONTH)
    ) AS last_month) - (SELECT type2 FROM (
        SELECT COUNT(CASE WHEN nationality_id = 24 THEN 1 END) AS type2 FROM table_x WHERE YEAR(START_DATE) = YEAR(NOW()) AND MONTH(START_DATE) = MONTH(NOW())
    ) AS current_month) AS type2,
    (SELECT type3 FROM (
        SELECT COUNT(CASE WHEN nationality_id = 25 THEN 1 END) AS type3 FROM table_x WHERE YEAR(START_DATE) = YEAR(NOW() - INTERVAL 1 MONTH) AND MONTH(START_DATE) = MONTH(NOW() - INTERVAL 1 MONTH)
    ) AS last_month) - (SELECT type3 FROM (
        SELECT COUNT(CASE WHEN nationality_id = 25 THEN 1 END) AS type3 FROM table_x WHERE YEAR(START_DATE) = YEAR(NOW()) AND MONTH(START_DATE) = MONTH(NOW())
    ) AS current_month) AS type3,
    (SELECT type4 FROM (
        SELECT COUNT(CASE WHEN nationality_id = 26 THEN 1 END) AS type4 FROM table_x WHERE YEAR(START_DATE) = YEAR(NOW() - INTERVAL 1 MONTH) AND MONTH(START_DATE) = MONTH(NOW() - INTERVAL 1 MONTH)
    ) AS last_month) - (SELECT type4 FROM (
        SELECT COUNT(CASE WHEN nationality_id = 26 THEN 1 END) AS type4 FROM table_x WHERE YEAR(START_DATE) = YEAR(NOW()) AND MONTH(START_DATE) = MONTH(NOW())
    ) AS current_month) AS type4;

可选调整 #

如果你不需要period字段来标识行含义，可以直接去掉该列，对应调整查询语句即可。

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