求MYSQL语句：1、遍历子树；2、得到子树所有叶子节点

[kinsey]

树结构如下：
1
/ \
2 3
/ \ / | \
8 9 4 5 10
/ \
6 7
/ \
11 12
/ \ / \
13 14 15 16

数据表(tb)内容如下：
id parent_id
1 NULL
2 1
3 1
4 3
5 3
6 4
7 4
8 2
9 2
10 3
11 6
12 6
13 11
14 11
15 12
16 12

Mysql如何通过sql语句获得
A. 节点3子树上的所有节点（4,5,6,7,10,11,12,13,14,15,16）
B. 节点3子树上的所有叶子节点（5,7,10,13,14,15,16）

用LEFT JOIN的方法只能得到根节点的。

[zsl5305256]

回去先！

[kinsey]

呵呵，高手们逗嫌分少没人回么？

[kinsey]

继续问：）

[kinsey]

没人愿意帮我么？:(

[josephlong]

我现在也在找解决的方案

[kinsey]

这个问题我还没解决。josephlong(josephlong)，您找到解决方案可否分享一下？谢谢先:)

[kinsey]

高手们，帮忙啊！

[kinsey]

终于搞定了，不过有两个问题：
1、只适合树的层次不太深的情况
2、如果树太大，会超出MySQL的嵌套层次限制（我用到的树大约4层，300个节点左右），我把SQL语句设到lev5的时候就出错，看来MySQL的处理能力还是有差距呀：）

遍历子树（得到从当前节点遍历所有子树节点所有路径）：
SELECT t1.id AS lev1, t2.id AS lev2, t3.id as lev3, t4.id as lev4,
t5.id as lev5, t6.id as lev6, t7.id as lev7, t8.id as lev8
FROM tb AS t1 LEFT JOIN tb AS t2 ON t2.parentid = t1.id
LEFT JOIN tb AS t3 ON t3.parentid = t2.id
LEFT JOIN tb AS t4 ON t4.parentid = t3.id
LEFT JOIN tb AS t5 ON t5.parentid = t4.id
LEFT JOIN tb AS t6 ON t6.parentid = t5.id
LEFT JOIN tb AS t7 ON t7.parentid = t6.id
LEFT JOIN tb AS t8 ON t8.parentid = t7.id
WHERE t1.id = 3;

找出某节点（3）的儿子节点和所有叶子
select b.c as child,
substring_index(b.l,',',-1) as leaves
from (select a.lev1 as p, a.lev2 as c,Concat_WS(',',a.lev1,a.lev2,a.lev3,a.lev4,a.lev5) as l
from (SELECT t1.id AS lev1, t2.id AS lev2, t3.id as lev3, t4.id as lev4,t5.id as lev5
FROM tb AS t1 LEFT JOIN tb AS t2 ON t2.parentid = t1.id
LEFT JOIN tb AS t3 ON t3.parentid = t2.id
LEFT JOIN tb AS t4 ON t4.parentid = t3.id
LEFT JOIN tb AS t5 ON t5.parentid = t4.id
LEFT JOIN tb AS t6 ON t6.parentid = t5.id
WHERE t1.id = 3) as a) as b

哪位高手看看可以有什么改进么？

[Chinalsd]

要用一个递归的吧。不过我也没解决。