MySQL使用单独的SELECT查询从多个表插入表 [英] MySQL Insert Into Table From Multiple Tables Using Separate SELECT Queries

问题描述

我有一个名为people_temp_1的表，其中包含:

I have a table named people_temp_1 that contains:

Name          Age          PersonID
John          25           1
Jane          32           2
Chris         47           3

另一个名为people_temp_2的文件，其中包含由表1中的PersonID链接的其他信息:

And another named people_temp_2 that contains additional information that is linked by the PersonID in table1:

ID          Profession          Location
1           Web Developer       Texas
2           Graphic Designer    North Carolina
3           Sales               California

我想创建一个名为people的新表，该表合并"两个表中的数据.我现在的操作方式是:

And I want to create a new table called people that "merges" the data from both tables. How I am doing this now is:

INSERT INTO people(name, age, profession, location)
SELECT people_temp_1.name AS name,
people_temp_2.age AS age,
(SELECT people_temp_2.profession FROM people_temp_2 WHERE people_temp_2.id = people_temp_1.personId) AS profession,
(SELECT people_temp_2.location FROM people_temp_2 WHERE people_temp_2.id = people_temp_1.personId) AS location
FROM people_temp_1

如您所见，我在插入查询中使用多个选择查询，以便通过personId获取相关数据.当我应该能够以某种方式一次查询people_temp_2表并使用其所有列时，执行多个选择查询似乎有点肮脏，但是我不知道该怎么做.

As you can see I am using multiple select queries in the insert into query in order to get data that is relevant via the personId. It seems a little dirty to be doing multiple select queries when I should somehow be able to query the people_temp_2 table once and use all of its columns, but I can't figure out how to do that.

有没有更好的方法来构造插入语句?

Is there a better way to structure that insert into statement?

推荐答案

这是SQL的基础知识之一-使用加入.在您的情况下，最好使用outer join，这样您就不会错过people_temp1中没有相应记录的人.

It's one of the basics of SQL - use join. In your case it's better to use outer join so you won't miss people from people_temp1 who don't have corresponding records in people_temp2:

insert into people(name, age, profession, location)
select
    p1.name,
    p1.age
    p2.profession,
    p2.location
from people_temp1 as p1
    left outer join people_temp2 as p2 on p2.id = p1.person_id

这篇关于MySQL使用单独的SELECT查询从多个表插入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！