如何不使用UNION ALL，在查询中定义行来创建CTE？

我的尝试 #

我想实现类似临时表的操作，但以下两种CTE写法都不符合预期：

第一种写法（仅查询CTE）：

WITH cte_name (dog, name) AS (
VALUES
  ('dog1', 'name1'),
  ('dog2', 'name2'),
  ('dog3', 'name3'),
  ('dog4', 'name4'),
  ('dog5', 'name5')
)
SELECT * FROM cte_name;

第二种写法（尝试向CTE插入数据，执行报错）：

WITH A AS (
    SELECT '2023-11-01' AS date, 'November' AS month
)
INSERT INTO A (date, month)
VALUES ('2023-10-01',  'October')

我找到了一种可行方法，但希望有更简洁的实现：

CREATE TEMPORARY TABLE A AS (
    SELECT '2023-11-01' AS date, 'November' AS month
);
INSERT INTO A (date, month)
VALUES ('2023-10-01',  'October')

解决方案 #

核心原因 #

CTE（公共表表达式）是只读的临时结果集，并非可写入的表对象，所以无法直接执行INSERT INTO CTE操作，这是第二种写法报错的根本原因。

更简洁的实现方式 #

根据不同使用场景，提供两种简洁写法：

场景1：一次性创建临时表并填充所有数据

如果不需要后续追加数据，直接把初始行和新增行合并写入临时表：

CREATE TEMPORARY TABLE A AS
WITH init_data AS (
    SELECT '2023-11-01' AS date, 'November' AS month
)
SELECT * FROM init_data
UNION ALL
SELECT '2023-10-01', 'October';

或者更直接的写法（PostgreSQL、MySQL 8.0+等多数数据库支持）：

CREATE TEMPORARY TABLE A (date DATE, month VARCHAR(20)) AS
VALUES
    ('2023-11-01', 'November'),
    ('2023-10-01', 'October');

场景2：需要后续继续向临时表插入数据

如果创建临时表后还要追加数据，你原本的写法已经很简洁，也可以进一步简化成：

CREATE TEMPORARY TABLE A AS SELECT '2023-11-01' AS date, 'November' AS month;
INSERT INTO A VALUES ('2023-10-01', 'October');

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