MySql_web树结构

很多网站的分类都是树结构，这里是一个理论上能实现无限级分类的树结构的方法。

创建库表

    create table category
    (
        id int primary key,
        name varchar(40) not null,
        lft int not null,
        rgt int not null
    );

加入数据

    insert into category(id,name,lft,rgt) values(1,"商品",1,18);
    insert into category(id,name,lft,rgt) values(2,"电视",2,7);
    insert into category(id,name,lft,rgt) values(3,"冰箱",8,11);
    insert into category(id,name,lft,rgt) values(4,"电脑",12,17);
    insert into category(id,name,lft,rgt) values(5,"长虹",3,4);
    insert into category(id,name,lft,rgt) values(6,"索尼",5,6);
    insert into category(id,name,lft,rgt) values(7,"西门子",9,10);
    insert into category(id,name,lft,rgt) values(8,"Thinkpad",13,14);
    insert into category(id,name,lft,rgt) values(9,"Dell",15,16);

取得树结构：

    select child.id,child.name,count(child.name) from category parent,category child where child.lft>=parent.lft and child.rgt<=parent.rgt group by child.name order by child.lft;