如何编写SQL按load_nr汇总s0_开头表行数（不使用UNION）

核心思路 #

因为表名是动态的（每日可能新增s0_开头的表），且要求不用UNION，必须通过动态生成SQL脚本实现：从系统元数据中自动获取目标表名，再拼接出关联查询语句，最终按load_nr汇总各表对应行数。

步骤拆解 #

1. 先获取所有s0_开头的表名

所有数据库都有存储表结构的系统元数据表，通过它筛选目标表：

• MySQL/MariaDB 示例：

  SELECT table_name 
  FROM information_schema.TABLES 
  WHERE table_name LIKE 's0_%' 
    AND table_schema = DATABASE(); -- 指定当前数据库，避免跨库查询
  

• PostgreSQL 示例：

  SELECT tablename 
  FROM pg_tables 
  WHERE tablename LIKE 's0_%' 
    AND schemaname = current_schema();
  

• SQL Server 示例：

  SELECT name AS table_name 
  FROM sys.tables 
  WHERE name LIKE 's0_%';
  

2. 生成关联查询的动态SQL

不用UNION的话，通过FULL OUTER JOIN把每个表的load_nr统计结果关联起来，确保所有表的load_nr都被覆盖，最终按load_nr分组统计行数。

以MySQL为例，用系统表的查询结果自动拼接SQL：

-- 初始化变量存储SQL片段
SET @sql = NULL;
SET @first_table = NULL;

-- 获取第一个s0_表（作为关联的基准表）
SELECT table_name INTO @first_table
FROM information_schema.TABLES 
WHERE table_name LIKE 's0_%' AND table_schema = DATABASE()
LIMIT 1;

-- 拼接SELECT部分的统计列：每个表的load_nr行数
SELECT GROUP_CONCAT(
  CONCAT('COUNT(', table_name, '.load_nr) AS ', table_name, '_count')
) INTO @select_cols
FROM information_schema.TABLES 
WHERE table_name LIKE 's0_%' AND table_schema = DATABASE();

-- 拼接JOIN部分：把其他表和基准表按load_nr做全连接
SELECT GROUP_CONCAT(
  CONCAT('FULL OUTER JOIN ', table_name, ' ON ', @first_table, '.load_nr = ', table_name, '.load_nr')
) INTO @join_clause
FROM information_schema.TABLES 
WHERE table_name LIKE 's0_%' AND table_schema = DATABASE() AND table_name != @first_table;

-- 拼接所有片段成完整SQL
SET @sql = CONCAT(
  'SELECT COALESCE(', 
  (SELECT GROUP_CONCAT(table_name, '.load_nr') FROM information_schema.TABLES WHERE table_name LIKE 's0_%' AND table_schema = DATABASE()),
  ') AS load_nr, ',
  @select_cols,
  ' FROM ', @first_table, ' ', @join_clause,
  ' GROUP BY COALESCE(',
  (SELECT GROUP_CONCAT(table_name, '.load_nr') FROM information_schema.TABLES WHERE table_name LIKE 's0_%' AND table_schema = DATABASE()),
  ')'
);

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

3. 适配不同数据库的注意点

• PostgreSQL：用EXECUTE format()执行动态SQL，全连接直接用FULL JOIN，系统表用pg_tables。
• SQL Server：用EXEC sp_executesql执行动态SQL，系统表用sys.tables。
• MySQL旧版本：如果不支持FULL OUTER JOIN，可以先收集所有表的load_nr到临时表，再对每个表做LEFT JOIN统计行数（避免用UNION）。

4. 性能优化建议

如果表数据量极大，直接多表JOIN可能较慢：

1. 先把每个表的load_nr统计结果存入临时表（每个表对应一个临时表，存储load_nr和行数）。
2. 把所有临时表按load_nr做全连接，得到最终结果。

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