如何使用映射将数据从分期插入到多个表

Question

我想要的是在mySQL表中插入数据，但我无法找到从一行建立关系的方法

我有一个登台表，其中具有以下数据：

            staging_table
+-----------+-------------------+-----------+-------------------+-----------+-------------------+-----------+-------------------+
|  p_name   |      p_details    |  child_1  |  child_details_1  |  child_2  |  child_details_2  |  child_3  |  child_details_3  |
+-----------+-------------------+-----------+-------------------+-----------+-------------------+-----------+-------------------+
|  parent_1 |  parent_details_1 | child_1.1 | child_details_1.1 | child_1.2 | child_details_1.2 |           |                   |
|  parent_2 |  parent_details_2 | child_2.1 | child_details_2.1 |           |                   |           |                   |
|  parent_3 |  parent_details_3 | child_3.1 | child_details_3.1 | child_3.2 | child_details_3.2 | child_3.3 | child_details_3.3 |
+-----------+-------------------+-----------+-------------------+-----------+-------------------+-----------+-------------------+

我想实现的是在两个表中插入数据，例如

            parent_table
+---+-----------+-------------------+
|id |   name    |      details      |
+---+-----------+-------------------+
| 1 |  parent_1 |  parent_details_1 |
| 2 |  parent_2 |  parent_details_2 |
| 3 |  parent_3 |  parent_details_3 |
+---+-----------+-------------------+

              child_table
+---+-----+-----------+-------------------+
|id | pid |   name    |      details      |
+---+-----+-----------+-------------------+
| 1 |  1  | child_1.1 | child_details_1.1 |
| 2 |  1  | child_1.2 | child_details_1.2 |
| 3 |  2  | child_2.1 | child_details_2.1 |
| 4 |  3  | child_3.1 | child_details_3.1 |
| 5 |  3  | child_3.2 | child_details_3.2 |
| 6 |  3  | child_3.3 | child_details_3.3 |
+---+-----+-----------+-------------------+

前两列适用于父，然后两列列属于孩子。因此，我必须在子表中插入儿童数据，但是如果它仅在列没有null的值时插入数据。

我尝试过。if的语句，但无法建立关系

但是我接下来我不知道。

Answer 1

应该很明显如何填充父表：

INSERT INTO parent_table (name, details)  
SELECT p_name name, p_details details FROM staging_table;

您可以以相同的方式创建子桌，但要做三对列的工会

INSERT INTO child_table (pid, name, details)
SELECT p.id pid, child_1 name, child_details_1 details
  FROM staging_table s
  JOIN parent_table p ON s.p_name = p.name AND s.p_details = p.details
 WHERE child_1 IS NOT NULL 
   AND child_details_1 IS NOT NULL
 UNION ALL
SELECT p.id pid, child_2 name, child_details_2 details
  FROM staging_table s
  JOIN parent_table p ON s.p_name = p.name AND s.p_details = p.details
 WHERE child_2 IS NOT NULL 
   AND child_details_2 IS NOT NULL
 UNION ALL
SELECT p.id pid, child_3 name, child_details_3 details
  FROM staging_table s
  JOIN parent_table p ON s.p_name = p.name AND s.p_details = p.details
 WHERE child_3 IS NOT NULL 
   AND child_details_3 IS NOT NULL;

请参阅查询的三个重复部分？这就是您处理不合规的输入表的方式。

请参阅JOIN ... ON ..子句？这就是您处理pid值进入您的child_table的方式。

我没有调试此。