如何在SQLite中对全库所有表的公共列执行union操作获取合并结果

核心思路 #

要实现跨任意数量表的公共列合并，分为两步处理：

• 先统计出数据库中所有用户表都包含的公共列
• 动态拼接所有表的公共列查询语句，通过UNION/UNION ALL关联得到最终合并结果

具体实现 #

步骤1：查询所有公共列 #

执行以下SQL可以得到所有表共有的列拼接字符串：

WITH all_tables AS (
    -- 筛选所有用户自建表，排除系统表
    SELECT name AS table_name
    FROM sqlite_master
    WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
),
all_table_columns AS (
    -- 遍历获取所有表的列信息
    SELECT 
        t.table_name,
        p.name AS column_name
    FROM all_tables t
    JOIN pragma_table_info(t.table_name) p
),
common_columns AS (
    -- 列出现次数等于总表数即为公共列
    SELECT column_name
    FROM all_table_columns
    GROUP BY column_name
    HAVING COUNT(DISTINCT table_name) = (SELECT COUNT(*) FROM all_tables)
)
-- 输出逗号分隔的公共列字符串，示例场景输出为 C1, C2
SELECT GROUP_CONCAT(column_name, ', ') AS common_columns_str FROM common_columns;

步骤2：生成最终合并查询 #

将步骤1得到的公共列字符串替换到下方[公共列字符串]占位符位置，执行后即可得到最终可直接运行的合并查询：

WITH all_tables AS (
    SELECT name AS table_name
    FROM sqlite_master
    WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
),
query_parts AS (
    SELECT 'SELECT [公共列字符串] FROM ' || table_name AS sql_part
    FROM all_tables
)
-- 需要去重时将 UNION ALL 改为 UNION 即可
SELECT GROUP_CONCAT(sql_part, ' UNION ALL ') AS final_union_sql FROM query_parts;

示例场景最终执行语句 #

对应题目中的t1、t2表场景，生成的最终查询如下，执行后即可得到预期结果：

SELECT C1, C2 FROM t1
UNION ALL
SELECT C1, C2 FROM t2;

注意说明 #

• 方案自动适配当前库内所有用户表，表数量变化时重新执行上述两个步骤即可生成新的查询，无需手动调整逻辑
• 建议提前保证公共列在不同表中的数据类型一致，避免隐式转换导致的数据异常
• 如需自动化执行，可以将上述两步逻辑封装到应用层代码或者SQLite自定义函数中，无需手动复制拼接结果

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