如何合并多个SELECT查询结果并按日期分组对齐补0

实现思路 #

• 先通过UNION提取三个查询结果中出现过的所有唯一日期，作为基准日期维度，无需依赖额外的全量日期表
• 以基准日期表分别左关联三个业务统计查询的结果
• 对关联后为空的统计字段，使用COALESCE函数替换为0即可符合要求

完整SQL实现 #

WITH create_stats AS (
    -- 替换为你实际的每日创建量查询语句
    SELECT date, num_created FROM your_create_query
),
update_stats AS (
    -- 替换为你实际的每日更新量查询语句
    SELECT date, num_update FROM your_update_query
),
delete_stats AS (
    -- 替换为你实际的每日删除量查询语句
    SELECT date, num_delete FROM your_delete_query
),
date_list AS (
    -- 聚合所有出现过的唯一日期
    SELECT date FROM create_stats
    UNION
    SELECT date FROM update_stats
    UNION
    SELECT date FROM delete_stats
)
SELECT
    dl.date,
    COALESCE(cs.num_created, 0) AS num_created,
    COALESCE(us.num_update, 0) AS num_update,
    COALESCE(ds.num_delete, 0) AS num_deleted
FROM date_list dl
LEFT JOIN create_stats cs ON dl.date = cs.date
LEFT JOIN update_stats us ON dl.date = us.date
LEFT JOIN delete_stats ds ON dl.date = ds.date
ORDER BY dl.date ASC;

方案说明 #

• 兼容性强：支持MySQL 8.0+、PostgreSQL、SQL Server、Oracle等所有支持CTE和COALESCE函数的主流数据库。如果使用不支持CTE的低版本数据库，可将CTE部分改写为嵌套子查询即可运行
• 性能优异：仅处理三个查询中实际存在操作的日期，无需生成冗余的连续日期序列，数据量越大性能优势越明显
• 结果准确：完全匹配要求的输出规则，无对应操作的字段自动填充0，不会遗漏任何存在操作的日期

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