来自多个表的 SQL INSERT INTO [英] SQL INSERT INTO from multiple tables

问题描述

这是我的表 1:

NAME       AGE        SEX        CITY             ID

Clara      22         f          New York         1
Bob        33         m          Washington       2
Sam        25         m          Boston           3

这是我的表 2:

NUMBER       ID
555-1111     1
555-2222     2
555-3333     3

现在我想要一个显示所有信息的表 3:

and now I want a table 3 which shows me all information:

NAME       AGE        SEX        CITY             ID        NUMBER

Clara      22         f          New York         1         555-1111
Bob        33         m          Washington       2         555-2222
Sam        25         m          Boston           3         555-3333

我首先尝试仅将表 1 中的值插入到表 3 中，然后我将表 2 中的值插入到表 3 中，并使用内部连接，其中 Id = Id.

I tried first to insert into table 3 only the values from table 1 and then I inserted into table 3 the values from table 2 with an inner join where Id = Id is.

INSERT INTO table3 { name, age, sex, city, id}
SELECT name, age, sex, city, id
FROM table 1



INSERT INTO table3 { name, age, sex, city, id, number}
SELECT name, age, sex, city, id, number
FROM table 2 p
INNER JOIN table 3 c ON c.Id = p.Id

但我得到的只是我的价值观的重复.我有 9 个条目，而不是 3 个条目，其中一些条目为空，有些只有数字，其余为空，有些是正确的.

But all I get is a duplication of my values. instead of having 3 entries, I have like 9 entries, which some have number null, some have only the number and the rest null, and some are correct.

希望有人能帮帮我

编辑

如果我现在有第三张这样的桌子:

If I am having now a third Table like this one:

NATIONALITY       ID

Canadian          1
American          2
French            3

如何将所有 3 个表合并为一个表?

How could I merge all 3 tables into one Table?

推荐答案

你只需要一个 INSERT:

You only need one INSERT:

INSERT INTO table4 ( name, age, sex, city, id, number, nationality)
SELECT name, age, sex, city, p.id, number, n.nationality
FROM table1 p
INNER JOIN table2 c ON c.Id = p.Id
INNER JOIN table3 n ON p.Id = n.Id

这篇关于来自多个表的 SQL INSERT INTO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！