如何利用information_schema.tables实现从同结构表table10_%批量提取指定列？

没法直接用单条静态SELECT语句实现你要的功能——静态SQL不允许动态指定表名（比如用通配符匹配表前缀）。但可以用动态SQL自动生成并执行跨表查询，不用手动写一堆重复的UNION语句。

下面是主流数据库的具体实现方式：

MySQL #

SET @sql = NULL;

SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'SELECT application, service, serviceid, item FROM ', 
        TABLE_NAME, 
        ' WHERE service IN (''SERVICE12'',''SERVICE204'') AND application = ''My Application'''
    ) SEPARATOR ' UNION '
) INTO @sql
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'table10_%';

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server #

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STRING_AGG(
    CONCAT(
        'SELECT application, service, serviceid, item FROM ', 
        QUOTENAME(TABLE_NAME), 
        ' WHERE service IN (''SERVICE12'',''SERVICE204'') AND application = ''My Application'''
    ), ' UNION '
)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'table10_%';

EXEC sp_executesql @sql;

若使用SQL Server 2016及更早版本，STRING_AGG不支持，可替换为FOR XML PATH方式拼接字符串

PostgreSQL #

DO $$
DECLARE
    sql TEXT;
BEGIN
    SELECT string_agg(
        CONCAT(
            'SELECT application, service, serviceid, item FROM ', 
            quote_ident(table_name), 
            ' WHERE service IN (''SERVICE12'',''SERVICE204'') AND application = ''My Application'''
        ), ' UNION '
    ) INTO sql
    FROM information_schema.tables
    WHERE table_name LIKE 'table10_%';

    EXECUTE sql;
END $$;

关键注意事项 #

• 所有匹配table10_%的表必须列结构完全一致（列名、数据类型需一一对应），否则会触发语法或类型不匹配错误。
• 执行语句的账号需具备访问INFORMATION_SCHEMA.TABLES的权限，以及所有目标表的查询权限。
• 若表名包含特殊字符，务必用QUOTENAME（SQL Server）、quote_ident（PostgreSQL）或反引号（MySQL）包裹，避免语法错误。

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