在MySQL中使用三个表填充多对多人-颜色表
我正在尝试在MySQL中规范化一个表,将一个具有许多相似列的表转换为具有两个列的多对多关系。我有以下表格:
人员:
+----+------+
| id | name |
+----+------+
| 1 | John |
| 2 | Anna |
| 3 | Leon |
+----+------+
Person_Temp:
+------+--------+--------+--------+--------+
| name | color1 | color2 | color3 | color4 |
+------+--------+--------+--------+--------+
| John | red | blue | green | |
| Anna | green | yellow | | |
| Leon | blue | red | | |
+------+--------+--------+--------+--------+
颜色:
+----+--------+
| id | name |
+----+--------+
| 1 | red |
| 2 | blue |
| 3 | green |
| 4 | yellow |
+----+--------+
我想在填充多对多关系表后删除PERSON_TEMP:
人色:
+-----------+----------+
| person_id | color_id |
+-----------+----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
| 2 | 4 |
| 3 | 2 |
| 3 | 1 |
+-----------+----------+
但是,我还没有找到我的查询的任何解决方案。我与人员ID的唯一关系是Person_Temp中的名称。我知道姓名在人称中是唯一的,因此使用它们进行查询不成问题。
我尝试使用此SQL,但它不起作用,因为Person_Temp没有id列。
INSERT INTO `person_color`
SELECT p.id, c.id
FROM (
SELECT id, color1 color
FROM person_temp
UNION
SELECT id, color2 FROM person_temp
UNION
SELECT id, color3 FROM person_temp
UNION
SELECT id, color4 FROM person_temp
UNION
SELECT id, color5 FROM person_temp
) p
JOIN color c
ON c.name = p.color;
解决方案
您的原始查询简单明了。除了从person_temp
中选择name
而不是id
,您只需将name
与id
中的id
连接在一起。您不需要单独对person_temp
执行所有这些联接。
INSERT INTO person_color
SELECT p.id AS person_id, c.id AS color_id
FROM (
SELECT name, color1 AS color FROM person_temp
UNION
SELECT name, color2 FROM person_temp
UNION
SELECT name, color3 FROM person_temp
UNION
SELECT name, color4 FROM person_temp
UNION
SELECT name, color5 FROM person_temp
) nc
JOIN color c
ON c.name = nc.color
JOIN person p
ON p.name = nc.name;
表行没有顺序,因此在插入中ORDER BY p.id, c.id
是没有意义的。输出行有顺序。
相关文章