mysql - 从一个表中选择多个列，从另一个表中获取相应的详细信息并将它们插入到另一个表中

Question

主表：包含所有详细信息，如proper_ID 和proper_Name 以及其他字段。

父表：有唯一的母亲和父亲的 ID，不存在适当的 ID 和适当的名称

子表：需要以与父表相同的顺序插入proper_ID和proper_Name，并避免重复条目。

Master_Table
---------------------------------------------------------------
Proper_ID | Proper_Name | Proper_Address | Proper_Phone |Proper_Zipcode
----------------------------------------------------------------
ABC_235 | Pansy Montgomery | 427 Preston Court| 1234| 5679
KWH_631 | Price Maxwell | 164 Conduit Boulevard| 8782| 7893
DEA_124 | Howard Kelly | 314 Agate Court| 3234| 1529
FAE_832 | Best Mcpherson | 325 Dorchester Road| 1582| 1861


Parent_Table
---------------------------------------------------
M_ID | F_ID | Picture_URL | Age_Group | Email
---------------------------------------------------
235| 832 | http://placehold.it/32x32| 45 | [e-mail]
631| 124 | http://placehold.it/32x32| 50 | [e-mail]

Output Expected:
Child_Table
---------------------------------------------------
Mother_ID | Mother_Name | Father_ID | Father_Name
---------------------------------------------------
ABC_235 | Pansy Montgomery | FAE_832 | Best Mcpherson
KWH_631 | Price Maxwell | DEA_124| Howard Kelly 


select mt.proper_id, mt.proper_name from master_table mt, parent_table pt
where mt.proper_id in (pt.m_id, pt.f_id)

proper_id | proper_name
-------------------------
ABC_235 | Pansy Montgomery
FAE_832 | Best Mcpherson
KWH_631 | Price Maxwell  
DEA_124| Howard Kelly 

标签： mysqlsequelize.js

Answer 1

您可以创建Child_TablewithCREATE ... SELECT语句。您需要进行JOIN两次Parent_Table，Master_Table一次获取母亲的详细信息，一次获取父亲的详细信息。请注意，您应该使用 ANSI 连接语法，逗号连接已被取代很长时间。

CREATE TABLE Child_Table AS
SELECT m1.Proper_ID AS Mother_ID,
       m1.Proper_Name AS Mother_Name,
       m2.Proper_ID AS Father_ID,
       m2.Proper_Name AS Father_Name
FROM Parent_Table p
JOIN Master_Table m1 ON SUBSTRING_INDEX(m1.Proper_ID, '_', -1) = p.M_ID
JOIN Master_Table m2 ON SUBSTRING_INDEX(m2.Proper_ID, '_', -1) = p.F_ID

那么你也能

 SELECT *
 FROM Child_Table

输出：

Mother_ID   Mother_Name         Father_ID   Father_Name
KWH_631     Price Maxwell       DEA_124     Howard Kelly
ABC_235     Pansy Montgomery    FAE_832     Best Mcpherson

SQLFiddle 上的演示

如果您只想为给定的一对M_ID和F_ID值生成一行，您可以在语句中放置一个索引，UNIQUE然后重复或它们：Mother_ID, Father_IDChild_TableCREATEIGNOREREPLACE

CREATE TABLE Child_Table (UNIQUE(Mother_ID, Father_ID)) IGNORE
... -- as above

SQLFiddle 上的演示