最近写项目,要写一个用户角色权限框架,特意研究了菜单的无限分级。比较推崇这一种方式,希望能给很多正在彷徨的同学一些帮助。
数据表:Category
category_id int pk
name varchar(20)
lft int
rit int
嵌套模型就是把本来是树形的数据变为嵌套的数据,并从左到右进行编号每个元素编一个左一个右。
以下是一些常用的查找sql语句
查找整个树
Select node.name From category as node,category as parent
Where node.lft between parent.lft and parent.rit
and parent.name = '根节点的名字'
order by node.lft
检索所有的叶子节点
Select name from category
where lft=rit+1
检索一个节点以下所有的节点
Select node.name from category as node,category as parent
where node.lft between parent.lft and parent.rit
and parent.name='节点的名字'
order by node.lft
检索节点的深度
Select node.name from category,(Count(parent.name)-1) As depth
from category as node
category as parent
where node.lft between parent.lft and parent.rit
group by node.name
order by node.lft
(Contact(Repeat(' ',Count(parent.name)-1),node,name))//可以用这个查询实现缩进
检索节点的直接子节点
Select node.name,(Count(parent.name)-(sub_tree.depth+1)) as depth
from category as node
category as parent
category as sub_parent
{
Select node.name,(Count(parent.name)-1) as depth
from category as node
category as parent
where node.lft between parent.lft and parent.rit
and node.name = "节点名称''
group by node.name
order by node.lft
}
as sub_tree
where node.lft between parent.lft and parent.rit
and node.lft between sub_parent.lft and sub_parent.rit
and sub_parent.name = sub_tree.name
group by node.name
having depth <=1
order by node.lft
新增节点
1一般的增加节点
Lock Table category write
Select @myRight := rit from category
where name = '左边的节点'
Update category set rit = rit+2 where rit>@myRight;
Update category set lft= lft+2 where lft>@myRight;
Insert Into category(name ,lft,rit) values('a',@myRight+1,@myRight+2)
Unlock Tables;
2添加叶子节点
Lock Table category write
Select @myLeft := left from category
where name = '要添加的子节点的父节点名称';
Update category set rit = rit+2 where rit>@myleft
Upadte category set lft = lft+2 where lft>@myleft
Insert Into category(name ,lft,rit) values('a',@myLeft+1,@myleft+2)
Unlock Tables;
删除节点
1删除叶子节点
Lock Table category write
Select @myleft := lft,@myRight := rit,@myWidth = rit-lft+1 from category
Where name = '要删除的叶子节点'
Delete from category where lft between @myleft and @myRight;
Update category set rit = rit-@myWidth Where rit>@myRight
Update category set lft = lft-@myWidth Where lft>@myRight
Unlock Table