时间戳

首页 > 解决方案 > SQL - 使用 WITH 在 INSERT INTO 上声明变量

sql - SQL - 使用 WITH 在 INSERT INTO 上声明变量

问题描述

我试图WITH在执行INSERT INTO. 我正在关注https://stackoverflow.com/a/16552441/2923526，它提供了以下SELECT查询示例：

WITH myconstants (var1, var2) as (
   values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
   OR something_else = var2;

我尝试了以下没有运气：

playground> CREATE TABLE foo (id numeric)
CREATE TABLE

playground> WITH consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (x)
column "x" does not exist
LINE 1: WITH consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (x)
                                                                ^

playground> WITH consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (consts.x)
missing FROM-clause entry for table "consts"
LINE 1: ...consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (consts.x)
                                                              ^

playground> WITH consts (x) AS (VALUES (2)) INSERT INTO foo VALUES (consts.x) FROM consts
syntax error at or near "FROM"
LINE 1: ...AS (VALUES (2)) INSERT INTO foo VALUES (consts.x) FROM const...
                                                             ^

playground> WITH consts (x) AS (VALUES (2)) INSERT INTO foo FROM consts VALUES (consts.x)
syntax error at or near "FROM"
LINE 1: WITH consts (x) AS (VALUES (2)) INSERT INTO foo FROM consts ...
                                                        ^

注意我是 SQL 的初学者，所以我希望避免使用 PLPGSQL 的解决方案

标签： sqlpostgresqlsql-insertcommon-table-expression

解决方案

我认为你想要：

WITH consts (x) AS (VALUES (2)) INSERT INTO foo SELECT x FROM consts

也就是说：该WITH子句创建一个派生表，然后您可以在主查询中使用它；所以你实际上需要SELECT ... FROM公用表表达式。

推荐阅读