sql - 如何重新键入表中的层次结构？

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，它删除重复项并重新键入cars 表中的层次结构基于 car_mapping 表中的 map_id 字段。这是生成的 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对于这个问题。有什么想法可以重新键入此层次结构吗？

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;

PS:在您的car_mapping 表中，您需要额外一行(下面的第一行)才能准确获得您想要的结果:

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