如何在MySQL中批量对日期命名子表求和并聚合？

针对批量聚合多表数据插入目标表的需求，无需手动编写所有UNION ALL语句，可通过动态SQL生成的方式高效实现，以下提供两种常用方案：

方案一：利用dm_ym_file_rules表生成动态SQL #

既然已有存储所有目标表名的规则表，直接从中提取表名拼接SQL即可，以MySQL为例：

1. 先执行以下语句生成完整的插入SQL：

-- 临时调整GROUP_CONCAT长度（若表数量多，避免截断）
SET SESSION group_concat_max_len = 1000000;

SELECT CONCAT(
    'INSERT INTO target_table
     SELECT a, b, c, SUM(d)
     FROM (',
    GROUP_CONCAT(
        CONCAT('SELECT a, b, c, SUM(d) FROM ', table_names, ' GROUP BY 1,2,3')
        SEPARATOR ' UNION ALL '
    ),
    ') temp GROUP BY 1,2,3;'
) AS dynamic_sql
FROM dm_ym_file_rules
-- 过滤日期范围内的目标表
WHERE start_date >= '2022-07-20' 
  AND end_date <= '2022-12-20';

2. 将查询结果中的dynamic_sql内容复制出来执行，即可完成所有表的聚合插入。

方案二：直接从数据库系统表匹配表名生成SQL #

如果规则表不可用，可通过数据库自带的系统表筛选符合命名规则的表，以MySQL为例：

SET SESSION group_concat_max_len = 1000000;

SELECT CONCAT(
    'INSERT INTO target_table
     SELECT a, b, c, SUM(d)
     FROM (',
    GROUP_CONCAT(
        CONCAT('SELECT a, b, c, SUM(d) FROM ', table_name, ' GROUP BY 1,2,3')
        SEPARATOR ' UNION ALL '
    ),
    ') temp GROUP BY 1,2,3;'
) AS dynamic_sql
FROM information_schema.TABLES
WHERE table_name LIKE 'dm_ym_file_2022%'
  -- 精准限定日期范围的表名
  AND table_name BETWEEN 'dm_ym_file_20220720' AND 'dm_ym_file_20221220';

注意事项 #

• 不同数据库的系统表名称不同：PostgreSQL用pg_tables，SQL Server用sys.tables，需对应调整查询语句；
• 确保所有子表的a、b、c、d字段结构完全一致，否则执行拼接后的SQL会报错；
• 若表数量极多，需根据数据库情况调整字符串拼接的长度限制（如MySQL的group_concat_max_len）。

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