您的位置: 首页  >  技术问答  >  根据另一张表中的编号在一张表中插入多行

根据另一张表中的编号在一张表中插入多行

分类: 技术问答 • 2022-04-22 10:48:29
问题描述：

我是第一次在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.



反向操作



上面是简单的汇总 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 statement

相关推荐