PostgreSQL如何选取多表指定列并将结果纵向合并

首先得给你提个小bug：你原来写的筛选列的条件COLUMN_NAME = 'geom' AND COLUMN_NAME = 'Name'是行不通的——一个列名不可能同时等于两个不同的值，得改成COLUMN_NAME IN ('geom', 'Name')或者用OR连接，才能正确找到包含这两列的表。

接下来要实现多表结果纵向合并，核心思路是动态生成UNION ALL拼接的查询语句，因为你的目标表是通过模糊匹配筛选出来的，没法硬写死。下面分步骤来：

1. 先确认同时包含geom和Name列的目标表 #

首先要确保每个目标表都同时有这两列，避免后续查询报错。执行这个语句找出符合要求的表：

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.columns
WHERE (TABLE_NAME LIKE '%HESA' OR TABLE_NAME LIKE '%HEWH')
AND COLUMN_NAME IN ('geom', 'Name')
GROUP BY TABLE_NAME
HAVING COUNT(DISTINCT COLUMN_NAME) = 2;

这个查询会过滤掉只含其中一列的表，保证后续拼接的查询都能正常执行。

2. 动态生成合并查询语句 #

根据你用的数据库，选对应的写法：

如果你用PostgreSQL： #

WITH target_tables AS (
    SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.columns
    WHERE (TABLE_NAME LIKE '%HESA' OR TABLE_NAME LIKE '%HEWH')
    AND COLUMN_NAME IN ('geom', 'Name')
    GROUP BY TABLE_NAME
    HAVING COUNT(DISTINCT COLUMN_NAME) = 2
)
SELECT string_agg(
    format('SELECT geom, Name FROM %I', TABLE_NAME),
    ' UNION ALL '
) AS union_query
FROM target_tables;

执行后会得到一个完整的SELECT ... UNION ALL SELECT ...语句，复制这个结果直接执行，就能得到所有表的geom和Name列纵向合并的结果。

如果你用MySQL： #

SELECT GROUP_CONCAT(
    CONCAT('SELECT geom, Name FROM `', TABLE_NAME, '`')
    SEPARATOR ' UNION ALL '
) AS union_query
FROM (
    SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.columns
    WHERE (TABLE_NAME LIKE '%HESA' OR TABLE_NAME LIKE '%HEWH')
    AND COLUMN_NAME IN ('geom', 'Name')
    GROUP BY TABLE_NAME
    HAVING COUNT(DISTINCT COLUMN_NAME) = 2
) AS target_tables;

同样，执行后拿到生成的拼接语句，直接运行就能得到合并后的结果。

其他数据库（比如SQL Server）： #

如果是SQL Server，可以用STRING_AGG或者FOR XML PATH来拼接，核心逻辑是一样的——先拿到目标表列表，再把每个表的查询语句用UNION ALL连起来。

举个SQL Server用STRING_AGG的例子：

WITH target_tables AS (
    SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.columns
    WHERE (TABLE_NAME LIKE '%HESA' OR TABLE_NAME LIKE '%HEWH')
    AND COLUMN_NAME IN ('geom', 'Name')
    GROUP BY TABLE_NAME
    HAVING COUNT(DISTINCT COLUMN_NAME) = 2
)
SELECT STRING_AGG(
    CONCAT('SELECT geom, Name FROM ', QUOTENAME(TABLE_NAME)),
    ' UNION ALL '
) AS union_query
FROM target_tables;

最后一步 #

把上面生成的union_query结果复制出来，直接执行，就能得到所有符合条件的表中geom和Name列的纵向合并结果了。

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