无限分级菜单之嵌套模型

       最近写项目，要写一个用户角色权限框架，特意研究了菜单的无限分级。比较推崇这一种方式，希望能给很多正在彷徨的同学一些帮助。

        数据表：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