MySQL查询7张同结构表XT1至XT7的m3字段并按日期汇总的实现方案

完整SQL语句 #

SELECT 
    IFNULL(t.date, 'Total') AS `Date`,
    SUM(CASE WHEN t.table_source = 'XT1' THEN t.m3 ELSE 0 END) AS `XT1.m3`,
    SUM(CASE WHEN t.table_source = 'XT2' THEN t.m3 ELSE 0 END) AS `XT2.m3`,
    SUM(CASE WHEN t.table_source = 'XT3' THEN t.m3 ELSE 0 END) AS `XT3.m3`,
    SUM(CASE WHEN t.table_source = 'XT4' THEN t.m3 ELSE 0 END) AS `XT4.m3`,
    SUM(CASE WHEN t.table_source = 'XT5' THEN t.m3 ELSE 0 END) AS `XT5.m3`,
    SUM(CASE WHEN t.table_source = 'XT6' THEN t.m3 ELSE 0 END) AS `XT6.m3`,
    SUM(CASE WHEN t.table_source = 'XT7' THEN t.m3 ELSE 0 END) AS `XT7.m3`,
    SUM(t.m3) AS `Subtotal XT1~7.m3`
FROM (
    SELECT date, m3, 'XT1' AS table_source FROM XT1
    UNION ALL
    SELECT date, m3, 'XT2' AS table_source FROM XT2
    UNION ALL
    SELECT date, m3, 'XT3' AS table_source FROM XT3
    UNION ALL
    SELECT date, m3, 'XT4' AS table_source FROM XT4
    UNION ALL
    SELECT date, m3, 'XT5' AS table_source FROM XT5
    UNION ALL
    SELECT date, m3, 'XT6' AS table_source FROM XT6
    UNION ALL
    SELECT date, m3, 'XT7' AS table_source FROM XT7
) t
GROUP BY t.date WITH ROLLUP;

方案优势 #

• 完全符合SQL标准规范，不需要修改sql_mode关闭ONLY_FULL_GROUP_BY校验，避免后续其他SQL出现非预期统计错误
• 兼容部分表缺失对应日期数据的场景，某张表无对应日期的m3值时会自动填0，不会丢失其他表的日期数据
• 自动生成每日合计值，以及最后的全量总计行，完全匹配预期输出格式
• 可扩展性强，后续新增同结构表只需要在UNION ALL部分增加对应SELECT语句，再补充对应CASE WHEN统计列即可

如果需要按月度汇总，只需要调整GROUP BY逻辑即可：

SELECT 
    IFNULL(DATE_FORMAT(t.date, '%Y-%m'), 'Total') AS `Date`,
    SUM(CASE WHEN t.table_source = 'XT1' THEN t.m3 ELSE 0 END) AS `XT1.m3`,
    -- 其余XT2~XT7的统计列保持不变
    SUM(t.m3) AS `Subtotal XT1~7.m3`
FROM (
    -- 内层UNION ALL部分保持不变
) t
GROUP BY DATE_FORMAT(t.date, '%Y-%m') WITH ROLLUP;

原有写法的问题 #

1. 用JOIN关联多表的方式强依赖所有表的日期完全对齐，INNER JOIN会直接过滤掉任意一张表缺失的日期，LEFT JOIN如果靠前的表缺失对应日期，后方表的同日期数据也会丢失
2. 关闭ONLY_FULL_GROUP_BY属于不规范的临时绕路方案，相当于关闭了SQL的字段合法性校验，很容易出现统计数据错误的问题

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