MySQL中进行树所有子节点的查询

[ACMAIN_CHM]

收集一下思路，提供者皆有分

在Oracle 中我们知道有一个 Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但很遗憾，在MySQL的目前版本中还没有对应的功能。

在MySQL中如果是有限的层次，比如我们事先如果可以确定这个树的最大深度是4, 那么所有节点为根的树的深度均不会超过4，则我们可以直接通过left join 来实现。

但很多时候我们无法控制树的深度。那么在MySQL中如何实现？（可用SQL语句，存储过程，函数，不用外部的程序如PHP，C..)

样例数据:

create table treeNodes
(
 id int primary key,
 nodename varchar(20),
 pid int
);

mysql> select * from treenodes;
+----+----------+------+
| id | nodename | pid  |
+----+----------+------+
|  1 | A        |    0 |
|  2 | B        |    1 |
|  3 | C        |    1 |
|  4 | D        |    2 |
|  5 | E        |    2 |
|  6 | F        |    3 |
|  7 | G        |    6 |
|  8 | H        |    0 |
|  9 | I        |    8 |
| 10 | J        |    8 |
| 11 | K        |    8 |
| 12 | L        |    9 |
| 13 | M        |    9 |
| 14 | N        |   12 |
| 15 | O        |   12 |
| 16 | P        |   15 |
| 17 | Q        |   15 |
+----+----------+------+
17 rows in set (0.00 sec)

[code=BatchFile] 1:A
+-- 2:B
| +-- 4:D
| +-- 5:E
+-- 3:C
+-- 6:F
+-- 7:G
8:H
+-- 9:I
| +-- 12:L
| | +--14:N
| | +--15:O
| | +--16:P
| | +--17:Q
| +-- 13:M
+-- 10:J
+-- 11:K [/code]

目前想到的两个方法，期望有更多的思路。

http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx

[yongyupost2000]

借 邹健老大的深度排序来显示处理吧

DECLARE @t_Level Table(ID int ,Level int,Sort varchar(8000))
DECLARE @Level int
Set @Level=0
INSERT @t_Level SELECT id,@Level,ID FROM treeNodes WHERE PID=0
WHILE @@ROWCOUNT >0
BEGIN
SELECT @Level=@Level+1
INSERT @t_Level SELECT A.ID,@Level,B.sort+A.ID FROM treeNodes A,@t_Level B
WHERE A.PID=B.ID AND B.Level=@Level-1
END

--显示结果
SELECT SPACE(B.Level*2)+"+--"+A.nodename
FROM treeNodes A,@t_Level B
WHERE A.ID=B.ID
ORDER BY B.Sort

[hqaht]

[SimonYeung]

学习

[fastopen20095]

路过,看看

[十豆三]

晕了，没有一个提供思路的。

[bwangel]

这种事情本来不该由数据库来实现,如果结点个数是有限的,强烈建议你以最快的速度一次性全读进内存,然后由程序来递归处理.

[ACMAIN_CHM]

[Quote=引用 41 楼 yongyupost2000 的回复:]
借 邹健老大的深度排序来显示处理吧

DECLARE @t_Level Table(ID int ,Level int,Sort varchar(8000))
DECLARE @Level int
Set @Level=0
INSERT @t_Level SELECT id,@Level,ID FROM treeNodes WHERE PID=0
WHILE @@ROWCOUNT >0
BEGIN
SELECT @Level=@Level+1
INSERT @t_Level SELECT A.ID,@Level,B.sort+A.ID FROM treeNodes A,@t_Level B
WHERE A.PID=B.ID AND B.Level=@Level…
[/Quote]

借鉴。 好。

[S_zxing]

这个真不会，还以为是打印二叉树呢
帮顶吧……

[fl49809045]

我有个建议，可以考虑添加两个字段 left_value,right_value
根 root 的初始值为1，2
.....
采取通过左右值技术来解决

希望对你有帮助

[老紫竹]

[Quote=引用 42 楼 fl49809045 的回复:]
我有个建议，可以考虑添加两个字段 left_value,right_value
根 root 的初始值为1，2
.....
采取通过左右值技术来解决

希望对你有帮助

[/Quote]


这个才是关键，这个结构可以用一个最简单的sql拿到所有你要的数据。
唯一的缺点就是插入和更新节点上下级关系时，比较麻烦。
但查询效率出奇的高。


详细情况请看我的图文介绍： 预排序遍历树算法的图文解释(modified preorder tree traversal algorithm)

[yuanwen1314]

up

[ACMAIN_CHM]

楼上好方法。

[jiahehao]

[Quote=引用 43 楼 bwangel 的回复:]
这种事情本来不该由数据库来实现,如果结点个数是有限的,强烈建议你以最快的速度一次性全读进内存,然后由程序来递归处理.
[/Quote]

我也这么认为。由程序来处理，就是一个比较简单的逻辑问题了，而且复杂程度比放在数据库里处理低了许多，可控性更强。难道在数据库里做效率会高一些吗？

[ACMAIN_CHM]

[Quote=引用 49 楼 jiahehao 的回复:]
引用 43 楼 bwangel 的回复:
这种事情本来不该由数据库来实现,如果结点个数是有限的,强烈建议你以最快的速度一次性全读进内存,然后由程序来递归处理.


我也这么认为。由程序来处理，就是一个比较简单的逻辑问题了，而且复杂程度比放在数据库里处理低了许多，可控性更强。难道在数据库里做效率会高一些吗？[/Quote]

也不是，因为在程度设计的时候经常中有这种需要，比较取出这个管理者所有的下属。一张表中的记录可以会有上万条（比如一万名员工的企业）。

[phpboy]

[Quote=引用 47 楼 java2000_net 的回复:]
引用 42 楼 fl49809045 的回复:
我有个建议，可以考虑添加两个字段 left_value,right_value
根 root 的初始值为1，2
.....
采取通过左右值技术来解决

希望对你有帮助




这个才是关键，这个结构可以用一个最简单的sql拿到所有你要的数据。
唯一的缺点就是插入和更新节点上下级关系时，比较麻烦。
但查询效率出奇的高。


详细情况请看我的图文介绍：预排序遍历树算法的图文解释(modified preorder tree traver…
[/Quote]

学习一下 受益非浅

[ACMAIN_CHM]

[Quote=引用 41 楼 yongyupost2000 的回复:]
借 邹健老大的深度排序来显示处理吧

DECLARE @t_Level Table(ID int ,Level int,Sort varchar(8000))
DECLARE @Level int
Set @Level=0
INSERT @t_Level SELECT id,@Level,ID FROM treeNodes WHERE PID=0
WHILE @@ROWCOUNT >0
BEGIN
SELECT @Level=@Level+1
INSERT @t_Level SELECT A.ID,@Level,B.sort+A.ID FROM treeNodes A,@t_Level B
WHERE A.PID=B.ID AND B.Level=@Level…
[/Quote]


已把这个方法改为MySQL的收录了。

[vyouzhi]

http://blog.bowenye.com/read.php?7

我这儿有PostgreSQL实现了，不过这个函数没仔细想，只是能用而已

[gshj121]

学习中。。。。。。。。。。。。。。。

[lovezx1028]

学习啦。

[y_cat]

[Quote=引用 47 楼 java2000_net 的回复:]
引用 42 楼 fl49809045 的回复:
我有个建议，可以考虑添加两个字段 left_value,right_value
根 root 的初始值为1，2
.....
采取通过左右值技术来解决

希望对你有帮助




这个才是关键，这个结构可以用一个最简单的sql拿到所有你要的数据。
唯一的缺点就是插入和更新节点上下级关系时，比较麻烦。
但查询效率出奇的高。

……
[/Quote]

如何更新节点??