mysql - 如何创建 MySQL 视图以通过相关表显示嵌套集模型中每个节点的所有路径？

Question

我有两个表，类别和产品。Categories 表是一个嵌套的集合模型。Products 表有一个唯一serial_number的字段。他们的架构是这样的：

分类：

+----+-----------+-----+-----+-------+-------------+
| id | parent_id | lft | rgt | depth |    title    |
+----+-----------+-----+-----+-------+-------------+
|  1 | Null      |   2 |   9 |     0 | Cloth       |
|  2 | 1         |   3 |   6 |     1 | Men's       |
|  3 | 2         |   4 |   5 |     2 | Suits       |
|  4 | 1         |   7 |   8 |     1 | Women's     |
|  5 | Null      |  10 |  13 |     0 | Electronics |
|  6 | 5         |  11 |  12 |     1 | TVs         |
+----+-----------+-----+-----+-------+-------------+

产品 ：

+-------------+---------------+
| category_id | serial_number |
+-------------+---------------+
|           3 |    5461354631 |
|           3 |    4521516545 |
|           4 |    8513453217 |
|           6 |    1235624165 |
+-------------+---------------+

我想要的是创建一个视图以显示所有序列号及其类别路径：

+---------------+-------------------+
| serial_number |       path        |
+---------------+-------------------+
|    5461354631 | Cloth/Men's/Suits |
|    4521516545 | Cloth/Men's/Suits |
|    8513453217 | Cloth/Women's     |
|    1235624165 | Electronics/TVs   |
+---------------+-------------------+

生成此视图的最佳查询是什么？

标签： mysqlnested-set-model