postgresql 在postgresql中将两个select语句添加到一个insert into语句中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20426967/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding two select statements into one insert into statement in postgresql
提问by Super_user_one
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;
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.
我试图采用 2 个选择语句并将数据插入到该临时表中。例如，我有一个名为 t1 的表，它提供前四个值，然后我希望临时表的下 3 个值来自另一个表。
So far I have:
到目前为止，我有：
insert into return_table
(Select var1, var2, var3, var4
from t1 where var1 = 10)
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.
这将成功地将 4 个值放入我的临时表中，然后将其余值保留为空。很好，所以当我尝试从另一个表插入最后三个变量时。例如
insert into return_table
(Select var1, var2, var3, var4
from t1 where var1 = 10, Select var5, var6, var 7
from t2 where var6 = 25)
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.
它会引发语法错误。我尝试了一些其他的语法更改，但我无法找出将这些 select 语句的两个结果插入同一行的正确语法。
Any help would be great!
任何帮助都会很棒！
回答by foibs
Two select commands separated by a comma is not valid SQL syntax. You can use joinor withstatements instead. Here's an example with with
用逗号分隔的两个选择命令不是有效的 SQL 语法。您可以使用joinorwith语句代替。这是一个例子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
One could make only one subquery with withbut I put them both to demonstrate the flexibility of being able to add as many tables as required.
一个人只能创建一个子查询，with但我把它们都放在一起是为了证明能够根据需要添加任意数量的表的灵活性。
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
另请注意，如果两个子查询中的任何一个返回的行数不同于一个，则上述示例（及其等效的连接）可能会产生奇怪的结果
回答by Dan G
Don't have an instance handy to test this, but what if you put 'UNION' between your select statements, so it would be one result set coming back?
没有一个方便的实例来测试这个，但是如果你在你的选择语句之间放置“UNION”，那么它会返回一个结果集怎么办？
