无需Union/Union All批量提取同结构带后缀SQL表数据

针对每日自动创建、仅表名后缀不同的同结构表，可通过数据库系统元数据+动态SQL实现自动合并，无需手动维护Union/CTE。以下分主流数据库给出具体实现：

SQL Server #

利用sys.tables获取符合命名规则的表名，动态拼接Union All语句执行：

DECLARE @sql NVARCHAR(MAX)

-- 拼接所有符合条件的表查询语句
SELECT @sql = STRING_AGG(
    CONCAT('SELECT date, id, revenue FROM ', QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)), '.', QUOTENAME(t.name)),
    ' UNION ALL '
)
FROM sys.tables t
WHERE t.name LIKE 'Mytable_name.%' -- 匹配表名前缀

-- 执行动态SQL
EXEC sp_executesql @sql

• 若使用SQL Server 2016及更早版本，替换STRING_AGG为FOR XML PATH写法：

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + 'SELECT date, id, revenue FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + QUOTENAME(t.name) + ' UNION ALL '
FROM sys.tables t
WHERE t.name LIKE 'Mytable_name.%'

-- 移除最后多余的UNION ALL
SET @sql = LEFT(@sql, LEN(@sql) - 10)

EXEC sp_executesql @sql

MySQL #

通过information_schema.tables筛选表，用GROUP_CONCAT拼接SQL：

SET @sql = NULL;

-- 生成合并查询语句
SELECT GROUP_CONCAT(
    CONCAT('SELECT `date`, `id`, `revenue` FROM `', table_schema, '`.`', table_name, '`')
    SEPARATOR ' UNION ALL '
) INTO @sql
FROM information_schema.tables
WHERE table_name LIKE 'Mytable_name.%'
AND table_schema = '你的数据库名'; -- 指定目标数据库

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

• 若表数量较多，需先调整GROUP_CONCAT长度限制：

SET SESSION group_concat_max_len = 1000000;

PostgreSQL #

使用pg_tables获取表信息，结合string_agg和动态执行：

DO $$
DECLARE
    sql TEXT;
BEGIN
    -- 拼接查询语句
    SELECT string_agg(
        format('SELECT date, id, revenue FROM %I.%I', schemaname, tablename),
        ' UNION ALL '
    ) INTO sql
    FROM pg_tables
    WHERE tablename LIKE 'Mytable_name.%'
    AND schemaname = 'public'; -- 指定目标架构

    -- 执行动态SQL
    EXECUTE sql;
END $$;

注意事项 #

• 确保所有匹配的表结构完全一致（字段名、类型、顺序相同），否则Union All会抛出错误。
• 执行动态SQL需具备访问系统表（如sys.tables、information_schema.tables）及目标表的权限。
• 长期优化建议：改用分区表替代每日建独立表的方案，分区表可自动按日期管理数据，查询更高效且无需动态SQL维护。

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