根据另一张表中的编号在一张表中插入多行 [英] Insert multiple rows in one table based on number in another table

问题描述

我是第一次在MacOSX上使用Postgres 9.3创建数据库。

I am creating a database for the first time using Postgres 9.3 on MacOSX.

假设我有表 A 和 B 。 A 开始为空，而 B 开始填充。我希望表 B 中列 all_names 中的条目数等于 number 表 A 中每个名称，如表 B 。因此，名称应该包含 all_names 和 number 中的每个唯一条目它的数量。我还不习惯这种语法，所以我真的不知道该怎么做。 生日列是多余的。

Let's say I have table A and B. A starts off as empty and B as filled. I would like the number of entries in column all_names in table B to equal the number for each names in table A like table B below. Thus names should contain each unique entry from all_names and number its count. I am not used to the syntax, yet, so I do not really know how to go about it. The birthday column is redundant.

表 A

Table A

names | number
------+--------
Carl  | 3
Bill  | 4
Jen   | 2

表 B

Table 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;

推荐答案

原始问题的答案

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);

由于引入了 LATERAL 您可以坚持使用标准SQL ：SRF从 SELECT 移至 FROM 列表：

Since the introduction of LATERAL in Postgres 9.3 you can do stick to standard SQL: the SRF moves from the SELECT to the FROM list:

INSERT INTO b (all_names, birthday)
SELECT a.names, current_date -- AS birthday ??
FROM   a, generate_series(1, a.number) AS rn

LATERAL 在这里是隐式的，如手册：

LATERAL is implicit here, as explained in the manual:

LATERAL 也可以在函数调用之前 FROM 项，但在这种情况下
是一个干扰词，因为在任何情况下函数表达式都可以引用
之前的FROM项。

LATERAL can also precede a function-call FROM item, but in this case it is a noise word, because the function expression can refer to earlier FROM items in any case.

反向操作

上面是简单的< a href = https://www.postgresql.org/docs/current/functions-aggregate.html rel = nofollow noreferrer>汇总 count（） ：

INSERT INTO a (name, number)
SELECT all_names, count(*)
FROM   b
GROUP  BY 1;

...适合您的最新问题。

... which fits your updated question.

请注意 count（*）和 count（all_names）之间的细微差别。前者无论如何都对所有行进行计数，而后者仅对 all_names不为空的行进行计数。如果您的列 all_names 被定义为 NOT NULL ，则两者均返回相同的值，但 count（ *）会更短，更快。

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_names is defined as NOT NULL, both return the same, but count(*) is a bit shorter and faster.

关于 GROUP BY 1 ：

• GROUP BY + CASE语句

• GROUP BY + CASE statement

这篇关于根据另一张表中的编号在一张表中插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！