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

  • Z时代
  • 2024-01-10
  • 分类：问答

我正在尝试使用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的解决方案的方法。

回答：

我认为您想要：

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

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

以上是 SQL-使用WITH在INSERT INTO上声明变量 的全部内容， 来源链接： utcz.com/qa/400058.html

回到顶部