如何在表格中重新设置层次结构？

Question

我有两个表格。 cars：包含关于汽车的层级数据。

+-----+-------------+-----------+
| id  | description | parent_id |
+-----+-------------+-----------+
|   1 | All cars    |         1 |
|  30 | Toyota      |         1 |
|  34 | Yaris       |        30 |
|  65 | Yaris       |        30 |
|  87 | Avensis     |        30 |
|  45 | Avensis     |        30 |
| 143 | Skoda       |         1 |
| 199 | Octavia     |       143 |
|  12 | Yeti        |       143 |
+-----+-------------+-----------+

car_mapping: 包含映射数据，其中重复的汽车（具有不同的id）被映射到一个id。

+--------+----------+--------+
| car_id | car_name | map_id |
+--------+----------+--------+
|     34 | Yaris    |      1 |
|     65 | Yaris    |      1 |
|     87 | Avensis  |      2 |
|     45 | Avensis  |      2 |
|    199 | Octavia  |      3 |
|     12 | Yeti     |      4 |
|     30 | Toyota   |      5 |
|    143 | Skoda    |      6 |
|      1 | All cars |      0 |
+--------+----------+--------+

现在，我们的想法是创建第三个表 cars_new，该表基于 cars 和 car_mapping 进行操作，以去除重复数据并根据 car_mapping 表中的 map_id 字段重新确定 cars 表中的层次结构关系。以下就是最终结果：cars_new。

+--------+----------+---------------+
| map_id | car_name | parent_map_id |
+--------+----------+---------------+
|      0 | All      |             0 |
|      1 | Yaris    |             5 |
|      2 | Avensis  |             5 |
|      3 | Octavia  |             6 |
|      4 | Yeti     |             6 |
|      5 | Toyota   |             0 |
|      6 | Skoda    |             0 |
+--------+----------+---------------+

这里是有关此问题的SQL Fiddle。您有什么想法来重新设计此层次结构的键值？

- jrara

---

+1 好问题，样本数据和示例代码！ - KM.

Answer 1

select distinct cm.map_id, cm.car_name, cm2.map_id parent_map_id
from cars c, car_mapping cm, car_mapping cm2
where c.id = cm.car_id
and c.parent_id = cm2.car_id(+)
order by cm.map_id;

提示：在你的car_mapping表中，你需要添加一行额外的数据（如下第一行），才能得到你想要的结果：

+--------+----------+--------+
| car_id | car_name | map_id |
+--------+----------+--------+
|     1  | All      |      0 |
|     34 | Yaris    |      1 |
|     65 | Yaris    |      1 |
Etc..

- Majid Laissi

---

谢谢，我添加了这一行。这个cm2.car_id(+)是什么？ - jrara

在我添加这一行之前，对于 car_id = 1，没有匹配的 map_id。因此，(+)（Oracle 中的外连接）告诉 Oracle 即使在 car_mapping 中没有与条件 and c.parent_id = cm2.car_id(+) 匹配的行，也要获取该行。现在有了这一行，就不需要这样做了。 - Majid Laissi

@jrara：请注意，使用 ANSI 标准的 left outer join 而不是专有的 (+) 运算符是强烈推荐的。 - user330315

Answer 2

根据 @Majid LAISSI 的答案，这似乎在 Oracle 和 SQL Server 中都有效：

select distinct cm.map_id, cm.car_name, cm2.map_id as parent_map_id
from cars c
left outer join car_mapping cm on c.id = cm.car_id
left outer join car_mapping cm2 on c.parent_id = cm2.car_id
order by cm.map_id;

- jrara

Answer 3

你没有层级结构，最好不要创建一个。注意到你的“汽车”表并没有描述汽车；它只是将一个字符串分配给一个数字（和另一个数字给该数字）。从一开始，“所有汽车”并不是一辆汽车，“丰田”是一家汽车制造商，而不是一辆汽车。

解决方案——这将有助于您的唯一性问题并简化您的查询——是为每个不同的事物使用一个表：

• 制造商{mfg_id，name}——例如GM、福特
• 品牌{make_id，name，mfg_id}——例如雪佛兰、林肯；链接到制造商
• 型号{name，make_id}——例如Yaris等；链接到品牌。

请确保在每个表中使“名称”唯一，以防止创建虚假的ID。

这将让您随着需要为这些事物分配新属性，例如它们生产的年份、销售数量或每个型号的门数。它还将让您防止“关系”从福特到GM或者将Yaris作为“所有汽车”的父级。

（顺便说一句，我建议您避免在表名中使用“map”或“mapping”，因为它并没有说明任何内容。每个表都将行中的元素相互关联。每个表都将键映射到其值。好消息是，在新设计中，您的car_mapping表将消失。）

至于如何转换现有的汽车表，这将是一个麻烦事。假设cars_mapping是正确的，您将能够插入每个表，加入并取最小(id)，同时按名称分组。您需要三个这样的查询，然后仔细检查是否存在错位。

- James K. Lowden