postgresql 根据另一个表中的数字在一个表中插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19127630/
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
Insert multiple rows in one table based on number in another table
提问by jO.
I am creating a database for the first time using Postgres 9.3 on MacOSX.
我第一次在 MacOSX 上使用 Postgres 9.3 创建数据库。
Let's say I have table Aand B. Astarts off as empty and Bas filled. I would like the number of entries in column all_namesin table Bto equal the numberfor each namesin table Alike table Bbelow. Thus namesshould contain each unique entry from all_namesand numberits count. I am not used to the syntax, yet, so I do not really know how to go about it. The birthdaycolumn is redundant.
假设我有 tableA和B. A开始时空空如也B。我希望表中列all_names中的条目数B等于下表中number每个names表中的条目数。因此，应包含每个唯一的入口和计数。我还不习惯语法，所以我真的不知道如何去做。该列是多余的。ABnamesall_namesnumberbirthday
Table A
桌子 A
names | number
------+--------
Carl | 3
Bill | 4
Jen | 2
Table B
桌子 B
all_names | birthday
-----------+------------
Carl | 17/03/1980
Carl | 22/08/1994
Carl | 04/09/1951
Bill | 02/12/2003
Bill | 11/03/1975
Bill | 04/06/1986
Bill | 08/07/2005
Jen | 05/03/2009
Jen | 01/04/1945
Would this be the correct way to go about it?
这是正确的方法吗？
insert into a (names, number)
select b.all_names, count(b.all_names)
from b
group by b.all_names;
回答by Erwin Brandstetter
Answer to original question
对原始问题的回答
Postgres allows set-returning functions (SRF) to multiply rows. generate_series()is your friend:
Postgres 允许设置返回函数 (SRF) 来乘以行。generate_series()是你的朋友：
INSERT INTO b (all_names, birthday)
SELECT names, current_date -- AS birthday ??
FROM (SELECT names, generate_series(1, number) FROM a);
Since the introduction of LATERALin Postgres 9.3you can do stick to standard SQL: the SRF moves from the SELECTto the FROMlist:
自LATERALPostgres 9.3引入以来，您可以坚持使用标准 SQL：SRF 从 移动SELECT到FROM列表：
INSERT INTO b (all_names, birthday)
SELECT a.names, current_date -- AS birthday ??
FROM a, generate_series(1, a.number) AS rn
LATERALis implicit here, as explained in the manual:
LATERAL这里是隐含的，如手册中所述：
LATERALcan also precede a function-callFROMitem, but in this case it is a noise word, because the function expression can refer to earlier FROM items in any case.
LATERAL也可以在函数调用FROM项之前，但在这种情况下它是一个干扰词，因为函数表达式在任何情况下都可以引用更早的 FROM 项。
Reverse operation
反向操作
The above is the reverse operation (approximately) of a simple aggregate count():
以上是简单聚合count()的反向操作（大约）：
INSERT INTO a (name, number)
SELECT all_names, count(*)
FROM b
GROUP BY 1;
... which fits your updated question.
...这适合您更新的问题。
Note a subtle difference between count(*)and count(all_names). The former counts all rows, no matter what, while the latter only counts rows where all_names IS NOT NULL. If your column all_namesis defined as NOT NULL, both return the same, but count(*)is a bit shorter and faster.
请注意count(*)和之间的细微差别count(all_names)。前者计算所有行，不管是什么，而后者只计算其中 的行all_names IS NOT NULL。如果您的列all_names定义为NOT NULL，则两者都返回相同的值，但count(*)更短且速度更快。
About GROUP BY 1:
关于GROUP BY 1：
