跨多表查询同名列：无关联气象站表的共享列检索需求

嘿，这个场景我太熟了——上百张结构相似但各自独立的气象表，要跨表查共享列的数据确实有点棘手，但咱们有几个实用的办法能搞定，从临时解决到长远优化都有：

既然你需要的列（stationName、date、maxTemp）在所有表里都一致，最直接的方式就是用UNION ALL把每个表的查询串起来：

SELECT stationName, date FROM table1 WHERE maxTemp > 30
UNION ALL
SELECT stationName, date FROM table2 WHERE maxTemp > 30
UNION ALL
SELECT stationName, date FROM table3 WHERE maxTemp > 30
-- ... 依次追加所有气象表

划重点：一定要用UNION ALL而不是UNION！ 后者会自动去重，性能比前者差很多，而你的气象记录跨表应该不会有重复数据吧？

不过上百张表手动写肯定要疯，所以下面这个动态生成查询的方案才是你的菜。

几乎所有主流数据库都支持动态SQL，我们可以利用系统内置的元数据表，自动获取所有气象表，然后拼接成完整的查询语句。下面是几个常用数据库的实现示例：

MySQL/MariaDB版本 #

SET @sql = NULL;
-- 自动拼接所有符合条件的表查询
SELECT GROUP_CONCAT(
    CONCAT('SELECT stationName, date FROM ', table_name, ' WHERE maxTemp > 30')
    SEPARATOR ' UNION ALL '
) INTO @sql
FROM information_schema.tables
WHERE table_schema = '你的数据库名' -- 替换成你的实际库名
AND table_name LIKE 'weather_station_%'; -- 如果表有统一前缀，用这个过滤，比如weather_xxx

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

PostgreSQL版本 #

DO $$
DECLARE
    table_rec RECORD;
    sql_query TEXT := '';
BEGIN
    -- 遍历所有目标表
    FOR table_rec IN 
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = 'public' -- 你的模式名，默认是public
        AND table_name LIKE 'weather_station_%'
    LOOP
        sql_query := sql_query || 'SELECT stationName, date FROM ' || quote_ident(table_rec.table_name) || ' WHERE maxTemp > 30 UNION ALL ';
    END LOOP;
    -- 去掉最后多余的"UNION ALL "
    sql_query := LEFT(sql_query, LENGTH(sql_query) - 10);
    -- 执行查询
    EXECUTE sql_query;
END $$;

SQL Server版本 #

DECLARE @sql NVARCHAR(MAX) = '';
-- 拼接所有表的查询语句
SELECT @sql = @sql + 'SELECT stationName, date FROM ' + QUOTENAME(table_name) + ' WHERE maxTemp > 30 UNION ALL '
FROM information_schema.tables
WHERE table_catalog = '你的数据库名'
AND table_name LIKE 'weather_station_%';

-- 移除末尾多余的UNION ALL
SET @sql = LEFT(@sql, LEN(@sql) - 10);
-- 执行动态SQL
EXEC sp_executesql @sql;

如果这些气象表不会频繁新增，你可以把上面动态生成的查询创建成一个视图，之后直接查视图就行：
以MySQL为例：

-- 先生成创建视图的SQL语句
SET @create_view_sql = CONCAT('CREATE VIEW all_weather_records AS ', @sql);
PREPARE stmt FROM @create_view_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 之后查询就超简单了！
SELECT stationName, date FROM all_weather_records WHERE maxTemp > 30;

优点是后续查询不用再折腾动态SQL，直接用视图；缺点是如果新增了气象表，需要手动更新视图的定义。

如果你的数据库支持分区表（MySQL、PostgreSQL、SQL Server都支持），从长期维护的角度看，最好把这些独立表合并成一个分区表：

• 先创建一个主表weather_records，包含所有共享列（stationName、date、maxTemp等），然后按stationName或者date字段做分区。
• 把原来各个表的数据导入到这个分区表对应的分区里。
• 之后查询就跟查普通表一样简单：

SELECT stationName, date FROM weather_records WHERE maxTemp > 30;

这个方案需要一次性做数据迁移，但后续的查询性能、维护成本都会好很多，适合长期使用的场景。

最后提个小细节：如果你的气象表没有统一前缀，可能需要手动筛选哪些是目标表，或者给这些表加统一的注释，然后通过元数据表过滤出带注释的表。

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