用树结构存储数据

来源：互联网发布：ubuntu 如何编辑文件编辑：程序博客网时间：2024/06/10 23:36

INSERT INTO nested_category
VALUES('ELECTRONICS',1,20,0),('TELEVISIONS',2,9,1),('TUBE',3,4,2),
('LCD',5,6,2),('PLASMA',7,8,2),('PORTABLE ELECTRONICS',10,19,1),
('MP3 PLAYERS',11,14,2),('FLASH',12,13,3),
('CD PLAYERS',15,16,2),('2 WAY RADIOS',17,18,2);

select * from nested_category

select * from nested_category where depth=0
select * from nested_category where lft>1 and rgt<24 and depth=1
select * from nested_category where lft>2 and rgt<9 and depth=2
select * from nested_category where lft>10 and rgt<13 and depth=2
select * from nested_category where lft>14 and rgt<23 and depth=2

--添加节点
declare @myRight int=0
SELECT @myRight= rgt FROM nested_category
WHERE name = 'TELEVISIONS';--查询要插入的叶子节点右面的兄弟节点的rgt值
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,depth) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2,1);

--为新加的 GAME CONSOLES 节点添加子节点
declare @myLeft int=0,@depth int=0
SELECT @myLeft= lft,@depth=depth FROM nested_category WHERE name = 'GAME CONSOLES';--查询该节点的 lft 值
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;--比他大的所有左节点加2
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;--比他大的所有右节点加2
INSERT INTO nested_category(name, lft, rgt,depth) VALUES('GAME CONSOLES', @myLeft + 1, @myLeft + 2,@depth+1);

--添加一个树结构
declare @myRight int=0,@depth int=0
SELECT @myRight= rgt,@depth=depth FROM nested_category WHERE name = 'GAME CONSOLES';
UPDATE nested_category SET rgt = rgt + 6 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 6 WHERE lft > @myRight;
INSERT INTO nested_category(name, lft, rgt,depth) values ('China',@myRight+1,@myRight+6,@depth)--根节点
INSERT INTO nested_category(name, lft, rgt,depth) values ('BeiJing',@myRight+2,@myRight+3,@depth+1)--第一个子节点
INSERT INTO nested_category(name, lft, rgt,depth) values ('ShangHai',@myRight+4,@myRight+5,@depth+1)--第二个子节点

--连同子节点一起删除
declare @myRight int=0,@myLeft int=0,@myWidth int=0
SELECT @myLeft = lft, @myRight = rgt, @myWidth = rgt - lft + 1
FROM nested_category
WHERE name = 'China';--查询要删除节点的左右值

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;

--只删除跟节点把根节点下的所有的子节点都放到被删除节点的根节点上
declare @myRight int=0,@myLeft int=0,@myWidth int=0
SELECT @myLeft = lft, @myRight = rgt, @myWidth = rgt - lft + 1
FROM nested_category
WHERE name = 'MP3 PLAYERS';--查询要删除节点的左右值

DELETE FROM nested_category WHERE lft = @myLeft;

UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1,depth=depth-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;

--删除根节点，并把其子节点放到指定的跟节点中

--有待更新.......

0 0