如何用SQL将多个临时表合并为一个表或临时表？

针对你需要合并多个CTE（WITH子句定义的临时表）为单一表或实现类似VIEW复用效果的需求，以下是几种可行方案：

1. 创建持久化实体表 #

直接将UNION合并后的结果写入新的实体表，适合需要长期复用数据的场景：

CREATE TABLE merged_result AS
WITH a_table AS (
    SELECT *
    FROM a
    JOIN b
    WHERE ...
),
b_table AS (
    SELECT *
    FROM c
    JOIN d
    WHERE ...
),
c_table AS (
    SELECT *
    FROM c
    JOIN d
    WHERE ...
)
-- 无需去重时替换为UNION ALL，大幅提升性能
SELECT * FROM a_table
UNION 
SELECT * FROM b_table
UNION 
SELECT * FROM c_table;

如果是SQL Server等数据库，使用SELECT ... INTO语法：

WITH a_table AS (
    SELECT *
    FROM a
    JOIN b
    WHERE ...
),
b_table AS (
    SELECT *
    FROM c
    JOIN d
    WHERE ...
),
c_table AS (
    SELECT *
    FROM c
    JOIN d
    WHERE ...
)
SELECT * INTO merged_result
FROM (
    SELECT * FROM a_table
    UNION 
    SELECT * FROM b_table
    UNION 
    SELECT * FROM c_table
) AS temp;

2. 创建全局临时表（跨会话复用） #

若数据库支持全局临时表（如SQL Server、PostgreSQL），可将合并结果写入此类临时表，实现类似VIEW的复用性且无需持久化：

• SQL Server语法：

WITH a_table AS (
    SELECT *
    FROM a
    JOIN b
    WHERE ...
),
b_table AS (
    SELECT *
    FROM c
    JOIN d
    WHERE ...
),
c_table AS (
    SELECT *
    FROM c
    JOIN d
    WHERE ...
)
SELECT * INTO ##global_merged_table
FROM (
    SELECT * FROM a_table
    UNION 
    SELECT * FROM b_table
    UNION 
    SELECT * FROM c_table
) AS temp;

后续任意会话均可查询##global_merged_table，直到所有引用该表的会话关闭。

• PostgreSQL语法：

CREATE TEMP TABLE global_merged_table ON COMMIT PRESERVE ROWS AS
WITH a_table AS (
    SELECT *
    FROM a
    JOIN b
    WHERE ...
),
b_table AS (
    SELECT *
    FROM c
    JOIN d
    WHERE ...
),
c_table AS (
    SELECT *
    FROM c
    JOIN d
    WHERE ...
)
SELECT * FROM a_table
UNION 
SELECT * FROM b_table
UNION 
SELECT * FROM c_table;

ON COMMIT PRESERVE ROWS确保事务提交后临时表不被删除，可在当前会话内多次复用。

3. 将完整逻辑封装到VIEW中 #

你之前创建VIEW失败，大概率是错误地试图基于独立临时表创建VIEW。实际上可以直接把所有CTE和UNION逻辑写入VIEW定义，每次查询VIEW时都会执行完整合并逻辑，完全符合复用需求：

CREATE VIEW merged_view AS
WITH a_table AS (
    SELECT *
    FROM a
    JOIN b
    WHERE ...
),
b_table AS (
    SELECT *
    FROM c
    JOIN d
    WHERE ...
),
c_table AS (
    SELECT *
    FROM c
    JOIN d
    WHERE ...
)
SELECT * FROM a_table
UNION 
SELECT * FROM b_table
UNION 
SELECT * FROM c_table;

后续只需执行SELECT * FROM merged_view即可获取合并结果，这是最接近VIEW复用需求的方案。

4. 用存储过程封装逻辑（适配大量CTE场景） #

面对50个CTE的冗长代码，可将逻辑封装到存储过程中，支持参数化查询或按需生成临时表：

-- 以MySQL为例
DELIMITER //
CREATE PROCEDURE get_merged_data()
BEGIN
    WITH a_table AS (
        SELECT *
        FROM a
        JOIN b
        WHERE ...
    ),
    b_table AS (
        SELECT *
        FROM c
        JOIN d
        WHERE ...
    ),
    -- 省略其余47个CTE定义
    c_table AS (
        SELECT *
        FROM c
        JOIN d
        WHERE ...
    )
    SELECT * FROM a_table
    UNION 
    SELECT * FROM b_table
    -- 省略其余UNION语句
    UNION 
    SELECT * FROM c_table;
END //
DELIMITER ;

调用时执行CALL get_merged_data();即可获取结果，也可在存储过程内创建临时表供后续操作使用。

注意事项 #

• 针对50个CTE的场景，优先检查是否有重复逻辑（如示例中b_table和c_table逻辑相同），提炼为通用CTE减少冗余。
• 优先使用UNION ALL替代UNION，除非确实需要去重，前者性能远高于后者。

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