在PostgreSQL中的一个insert into语句中添加两个select语句 [英] Adding two select statements into one insert into statement in postgresql

问题描述

我通过以下方式制作了一个临时表:

I have made a temporary table through:

create temporary table return_table 
(
   p1 BIGINT, 
   p2 VARCHAR(45), 
   p3 VARCHAR(45), 
   p4 VARCHAR(45), 
   p5 VARCHAR(45), 
   p6 float, 
   p7float
) on commit drop;

我正在尝试采用2条s​​elect语句并将数据插入到该临时表中.例如，我有一个名为t1的表，它提供前四个值，然后我希望临时表的后三个值来自另一个表.

Im trying to take 2 select statements and insert data into that temporary table. For example, I have a table named t1 which provides the first four values, and then I want the next 3 values for the temporary table to come from another table.

到目前为止，我有:

insert into return_table 
(Select var1, var2, var3, var4 
 from t1 where var1 = 10)

这将成功地将4个值放入我的临时表，然后将其余值保留为空.很好，所以当我尝试从另一个表插入最后三个变量时.例如

That will successfully put 4 values into my temporary table and then leave the rest null. That's fine, so when I attempt to insert the last three variables from another table. e.g.

insert into return_table 
(Select var1, var2, var3, var4 
 from t1 where var1 = 10, Select var5, var6, var 7 
 from t2 where var6 = 25)

它引发语法错误.我已经尝试了其他一些语法上的更改，但是我想不出在同一行中插入这些select语句的两个结果的正确语法.

It throws a syntax error. I've tried a few other syntactical changes, but I can't figure out the right syntax for inserting both results of those select statements on the same row.

任何帮助都会很棒！

推荐答案

两个以逗号分隔的选择命令不是有效的SQL语法.您可以改用join或with语句.这是with

Two select commands separated by a comma is not valid SQL syntax. You can use join or with statements instead. Here's an example with with

insert into return_table 
WITH t1 AS (
    Select var1, var2, var3, var4 from t1 where var1 = 1
  ), t2 AS (
    Select var5, var6, var7 from t2 where var6 = 6
  )
select t1.var1, t1.var2, t1.var3, t1.var4, t2.var5, t2.var6, t2.var7 from t1,t2

一个人只能用with进行一个子查询，但是我把它们都放在了一起，以展示能够根据需要添加尽可能多的表的灵活性.

One could make only one subquery with with but I put them both to demonstrate the flexibility of being able to add as many tables as required.

请注意，列出插入表的所有列是一个很好的习惯，

Please note that it is a very good practice to list all the columns of the table that you are inserting into,

e.g. `insert into return_table (p1, p2, p3, p4, p5, p6, p7) ...`

养成习惯，可以避免很多潜在的麻烦和头痛.

You will avoid a lot of potential trouble and headaches if you make a habit of it.

还请注意，如果两个子查询中的任何一个返回的行数都不相同，则上面的示例(及其等效的联接)可能会产生时髦的结果

Also please note that the above example (and it's join equivalent) may produce funky results if any of the two subqueries returns a row count different than one

这篇关于在PostgreSQL中的一个insert into语句中添加两个select语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！