MySQL的按优先级等效连接 [英] Connect By Prior Equivalent for MySQL

问题描述

全部

我在表中有三个字段，用于定义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为根的行.
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 JOINs)联接到最大可能深度，然后清理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.

这篇关于MySQL的按优先级等效连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！