关于nested sets树

原文:http://www.eastphp.com/2052/
作者在检索子树的深度时用的sql语句有点复杂,几个join,还有个子查询。
我的SQL是这样,只用二个join。
select s.name,count(*)-1 as depth from nested_category as n,nested_category as s
where n.lft between 10 and 19
and s.lft between n.lft and n.rgt group by s.name
注意上面的10,19是某条记录的左右值（这二个值通常不难获得）。同样可以检索整树，只要改变左右值为根的左右值。

于是，检索节点的直接子节点也将方便。
select s.name,count(*)-1 as depth from nested_category as n,nested_category as s
where n.lft between 10 and 19
and s.lft between n.lft and n.rgt group by s.name
having depth=1