本文介绍了一种使用两个JOIN操作来简化SQL查询的方法，该方法可以有效地检索子树的深度，并提供了检索节点直接子节点的便利方式。
原文: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
作者在检索子树的深度时用的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
扫一扫
945
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
