按 MySQL 的先前等效项连接

2021-11-20 00:00:00 sql mysql stored-procedures

全部，

我在一个表中有三个字段，它们定义了 MySQL 数据库 5.0 版中存在的父子关系.表名是tb_Tree，它有以下数据:

I have three fields in a table that define a parent child relationship present in a MySQL database version 5.0 . The table name is tb_Tree and it has the following data:

Table Name: tb_Tree

Id | ParentId | Name
--------------------
1  | 0        | Fruits
2  | 0        | Vegetables
3  | 1        | Apple
4  | 1        | Orange
5  | 2        | Cabbage
6  | 2        | Eggplant

如果指定了 ParentId，我如何编写查询来获取所有孩子.请注意，给出的表条目只是示例数据，它们可以包含更多行.Oracle 有一个CONNECT BY PRIOR"子句，但我没有找到任何与 MySQL 类似的东西.谁能给点建议?

How do I write a Query to get all the children if a ParentId is specified. Note that the table entries given are just sample data and they can have many more rows. Oracle has a "CONNECT BY PRIOR" clause, but I didn't find anything similar for MySQL. Can anyone please advise?

谢谢

推荐答案

MySQL 不支持递归查询，因此您必须以艰难的方式去做:

MySQL doesn't support recursive queries so you have to do it the hard way:

  1. 选择 ParentID = X 的行，其中 X 是您的根.
  2. 从 (1) 中收集 Id 值.
  3. 对 (2) 中的每个 Id 重复 (1).
  4. 手动递归，直到找到所有叶节点.
  1. Select the rows where ParentID = X where X is your root.
  2. Collect the Id values from (1).
  3. Repeat (1) for each Id from (2).
  4. Keep recursing by hand until you find all the leaf nodes.

如果您知道最大深度，那么您可以将您的表与自身(使用 LEFT OUTER JOIN)连接到最大可能深度，然后清除 NULL.

If you know a maximum depth then you can join your table to itself (using LEFT OUTER JOINs) out to the maximum possible depth and then clean up the NULLs.

您还可以将树表示更改为 嵌套集.

You could also change your tree representation to nested sets.

相关文章