mysql - MySql 的递归查询 - 获取所有子树

Question

让我们假设有一家生产产品的工厂。工厂有产品目录，因此 tbl_catalog 将是。

+----------------+--------------+-----------------------------------------------+
| catalog_number | catalog_name | details                                       |
+----------------+--------------+-----------------------------------------------+
|              1 | LaptopX      | Full assembled laptop - X                     |
|              2 | Top Half     | Where the screen & webcam are                 |
|              3 | Bottom Half  | Where mothereboard, and rest of the parts are |
|              4 | WebCam       | WebCam for laptopX                            |
|              5 | LcdX         | Lcd screen for laptopX                        |
|              6 | Keyboard     | set of keys                                   |
|              7 | SSD          | Place to store data                           |
|              8 | Touchpad     | If there is no mouse connected                |
|              9 | DVD          | to play music and watch movies                |
|             10 | Lazer Beam   | a must have for our DVD                       |
|             11 | EngineX      | EngineX will spin our DVD with no problem     |
+----------------+--------------+-----------------------------------------------+

产品由目录中的其他产品组成 - 表示为树，对于我们的示例，tbl_catalog_tree 将是:

+-----------+----------+
| parent_id | child_id |
+-----------+----------+
|         1 |        2 |
|         1 |        3 |
|         2 |        4 |
|         2 |        5 |
|         3 |        6 |
|         3 |        7 |
|         3 |        8 |
|         3 |        9 |
|         9 |       10 |
|         9 |       11 |
+-----------+----------+

那是目录。现在让我们组装一些产品。我们需要另一个表 - assembly:

+------+----------------+--------------+-------+
| id   | catalog_number | assembled_by | qc_by |
+------+----------------+--------------+-------+
|  100 |             11 | Joe          | Dan   |
|  101 |             11 | Joe          | Dan   |
|  102 |             11 | Joe          | Dan   |
|  200 |             10 | Joe          | Dan   |
|  201 |             10 | Joe          | Dan   |
|  201 |             10 | Joe          | Dan   |
|  300 |              9 | Mike         | Dan   |
|  301 |              9 | Mike         | Dan   |
|  302 |              9 | Mike         | Dan   |
+------+----------------+--------------+-------+

但我们仍然不知道DVD的子部分之间有什么联系，所以我们需要tbl_assembly_tree:

+-----------+----------------------+----------+
| parent_id | child_catalog_number | child_id |
+-----------+----------------------+----------+
|       302 |                   11 | 100      |
|       302 |                   10 | 200      |
|       301 |                   11 | 101      |
|       301 |                   10 | 201      |
|       300 |                   11 | 102      |
|       300 |                   10 | 202      |
+-----------+----------------------+----------+

因为子树可以是不同目录的一部分(比如这张 DVD 可以满足 laptopX 的一部分和 future 的 laptopY)我们需要知道特定组件的子目录号是多少(而不是指望目录树结构).

我的问题是:如何查询以便获得产品及其所有子树产品？如果我想在数据库中查询目录号的所有组合项目，并让每个装配体递归地知道谁构建和谁对所有子部件进行质量控制，在上面的示例中，如果我查询 DVD，我希望答案是喜欢:

+----+---------+----------+---------+--------+------------+----------+--------+-------+
| id | cat_num | cat_name | assy_by | sub_id |sub_cat_num | sub_name | ass_by | qc_by |
+----+---------+----------+---------+--------+------------+----------+--------+-------+
| 300|     9   |   DVD    |  Mike   |   102  |  11        | EngineX  |  Joe   | Dan   |
| 300|     9   |   DVD    |  Mike   |   202  |  10        | Lazer B  |  Joe   | Dan   |
| 301|     9   |   DVD    |  Mike   |   101  |  11        | EngineX  |  Joe   | Dan   |
| 301|     9   |   DVD    |  Mike   |   201  |  10        | Lazer B  |  Joe   | Dan   |
| 302|     9   |   DVD    |  Mike   |   100  |  11        | EngineX  |  Joe   | Dan   |
| 302|     9   |   DVD    |  Mike   |   200  |  10        | Lazer B  |  Joe   | Dan   |
+----+---------+----------+---------+--------+------------+----------+--------+-------+

当然，如果所有目录都只有一个级别，我不会在这里提出这个问题，但我需要它是递归的，所以我可以看到我选择的目录号的所有程序集子树。

我构建了一个 sqlfiddle for the above example并希望有人能在这方面帮助我

Answer 1

想到的技巧是多次加入同一张 table 。

这将为您提供您在问题中陈述的结果:

SELECT
    d.parent_id as id,
    a.catalog_number,
    a.catalog_name,
    e.assembled_by,
    d.child_id as sub_id,
    b.child_id as sub_cat_num,
    c.catalog_name as sub_name,
    f.assembled_by as sub_assemly_by,
    e.qc_by

FROM
    catalog as a, catalog_tree as b, 
    catalog as c, assembly_tree as d, 
    assembly as e , assembly as f

WHERE 
    a.catalog_name = 'DVD'
    and a.catalog_number = b.parent_id
    and b.child_id = c.catalog_number
    and b.child_id = d.child_catalog_number
    and d.parent_id = e.id
    and d.child_id = f.id
ORDER BY 1