树状结构表的集合式存储方法

最近工作中遇到了一个树状结构表的处理问题.由于原来设计的模式是父级代码类型的.所以导致有些问题出现.

记得以前用过集合式的存储方式,具体方法记不清了.于是在网上搜.............

最后找到了mysql网站上一篇文章(http://dev.mysql.com/tech-resources/articles/hierarchical-data.html).

讲的很透彻.特记录在此.以备自己查阅,如对别人有帮助更好了.

 

-- 建立表
CREATE TABLE `nested_category` (
  `category_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL,
  `lft` INT(11) NOT NULL,
  `rgt` INT(11) NOT NULL,
  PRIMARY KEY  (`category_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 插入数据
INSERT INTO `nested_category` (`category_id`, `name`, `lft`, `rgt`) VALUES('1','电器','1','20');
INSERT INTO `nested_category` (`category_id`, `name`, `lft`, `rgt`) VALUES('2','电视','2','9');
INSERT INTO `nested_category` (`category_id`, `name`, `lft`, `rgt`) VALUES('3','晶体管电视','3','4');
INSERT INTO `nested_category` (`category_id`, `name`, `lft`, `rgt`) VALUES('4','液晶电视','5','6');
INSERT INTO `nested_category` (`category_id`, `name`, `lft`, `rgt`) VALUES('5','等离子电视','7','8');
INSERT INTO `nested_category` (`category_id`, `name`, `lft`, `rgt`) VALUES('6','便携式电器','10','19');
INSERT INTO `nested_category` (`category_id`, `name`, `lft`, `rgt`) VALUES('7','MP3','11','14');
INSERT INTO `nested_category` (`category_id`, `name`, `lft`, `rgt`) VALUES('8','微型Mp3','12','13');
INSERT INTO `nested_category` (`category_id`, `name`, `lft`, `rgt`) VALUES('9','CD唱片机','15','16');
INSERT INTO `nested_category` (`category_id`, `name`, `lft`, `rgt`) VALUES('10','对讲机','17','18');
 

-- 检索整棵树
SELECT node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = '电器'
ORDER BY node.lft;

-- 得到所有最顶层叶子.
SELECT NAME
FROM nested_category
WHERE rgt = lft + 1;

-- 得到某个节点的所有父级(含自己)
SELECT parent.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'Mp3'
ORDER BY parent.lft;


-- 得到某父级下的所有子孙列表.(含自己),格式同prefix_tw_goal_plus中数据!!
SELECT parent.name AS ParentName ,node.name AS childName
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
ORDER BY parent.category_id ,node.category_id;

-- 得到所有节点的深度
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

-- 得到深度,并缩进显示
SELECT CONCAT( REPEAT('   ', COUNT(parent.name) - 1), node.name) AS NAME,  (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

-- 相对某个子节点的深度.而不是相对于根节点的深度.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
    nested_category AS parent,
    nested_category AS sub_parent,
    (
        SELECT node.name, (COUNT(parent.name) - 1) AS depth
        FROM nested_category AS node,
        nested_category AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = '便携式电器'
        GROUP BY node.name
        ORDER BY node.lft
    )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;

-- 只显示直接下级.不显示深层的下级:只要儿子不要孙子.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
    nested_category AS parent,
    nested_category AS sub_parent,
    (
        SELECT node.name, (COUNT(parent.name) - 1) AS depth
        FROM nested_category AS node,
        nested_category AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = '便携式电器'
        GROUP BY node.name
        ORDER BY node.lft
    )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1/*如果不要自己,则HAVING depth =1*/
ORDER BY node.lft;

-- 新增一个节点 ---------------------------------------------------
-- 先锁定表
LOCK TABLE nested_category WRITE;

-- 要插入到电视之后,则得到'电视'的右值(解释:如果是增加电视的一个子<这里变为第一个>,则可用左值.)
SELECT @myRight := rgt FROM nested_category
WHERE NAME = '电视';

-- 空出2个数字来:即后面的所有值都加2.
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

-- 在空挡处插入新节点
INSERT INTO nested_category(NAME, lft, rgt) VALUES('游戏机', @myRight + 1, @myRight + 2);

-- 解锁表.
UNLOCK TABLES;

-- 删除某组节点(叶子或树枝) ------------------------------------------
-- 锁表
LOCK TABLE nested_category WRITE;

-- 得到左值,右值,宽度
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE NAME = '游戏机';

-- 删除树枝
DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

-- 缩进其他节点的左右值:根据宽度.
UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

-- 解锁
UNLOCK TABLES;


-- 删除某个节点,但不删除其子孙,子孙变为上级的子孙,如解雇了一个领导,但下属不解雇-------
-- 锁表
LOCK TABLE nested_category WRITE;

-- 得到左值,右值,宽度
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE NAME = '游戏机';

-- 删除本节点:不删子孙哦.
DELETE FROM nested_category WHERE lft = @myLeft;

-- 子孙提高一级.
UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight;

-- 缩进其他节点的左右值:根据宽度.
UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;

-- 解锁
UNLOCK TABLES;

-- 其他的详细说明可以参考教程<Joe Celko's Trees and Hierarchies in SQL for Smarties> --------