如何在同一条SQL查询中使用多个WITH语句和多个INSERT语句

报错根本原因 #

你写的WITH子句定义的CTE（公共表表达式）作用域仅属于它后面紧跟的第一条SQL语句。你拆分了两条独立的INSERT语句，第一条INSERT执行完成后，你定义的table_1_items和table_2_items就会被销毁，第二条INSERT自然找不到table_2_items关系。

方案1：使用可写CTE整合为单条语句（适配PostgreSQL等支持该特性的数据库） #

支持数据修改类CTE的数据库，可以把多个插入动作都整合到同一条WITH语句的上下文中，修改后的写法如下：

WITH table_1_items AS (
  SELECT * FROM example_table_1
),
table_2_items AS (
  SELECT * FROM example_table_2
),
-- 第一个插入作为CTE的一部分执行
insert_1 AS (
  INSERT INTO other_table_1 SELECT * FROM table_1_items
)
-- 最后执行第二个插入作为主语句
INSERT INTO other_table_2 SELECT * FROM table_2_items;

注意：你原写法里INSERT INTO xxx FROM xxx不是标准SQL语法，需要补全SELECT *改为INSERT INTO xxx SELECT * FROM xxx。

方案2：使用临时表适配所有数据库 #

如果你的数据库不支持可写CTE，可以改用临时表存储查询结果，临时表的生命周期覆盖当前会话/整个事务，可被多条INSERT语句调用：

-- 创建临时表存储两个查询的结果
CREATE TEMP TABLE table_1_items AS SELECT * FROM example_table_1;
CREATE TEMP TABLE table_2_items AS SELECT * FROM example_table_2;

-- 顺序执行插入操作
INSERT INTO other_table_1 SELECT * FROM table_1_items;
INSERT INTO other_table_2 SELECT * FROM table_2_items;

-- 临时表会在会话结束后自动清理，也可手动删除
DROP TABLE IF EXISTS table_1_items, table_2_items;

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