结构树树表设计二叉树.doc-道客多多

资源描述

1、The Nested Model 树形结构先把树按水平的方式摆开，如下图然后按前序遍历的方法，为每一个结点编号，每一个结点都有左、右的编号，根结点从 1 开始开始计算。我们分别把左右编号称为左值和右值。根据前序遍历的规律，我们可以知道，每一个结点的子结点的左值比父结点左值大而比父结点的右值小。例如 Fruit(2,11) 的子结点 Red(3,6)、Cherry(4,5)、 Yellow(7,10)、Banana(8,9) ，可以看到Red、Cherry、Yellow、Banana 的左值均比 Fruit 大，而右值均比 Fruit 小。数据库设计如下：Java 代码 1. CREATE TA

2、BLE nested_category ( 2. id int(11) NOT NULL AUTO_INCREMENT, 3. name varchar(16) DEFAULT NULL, 4. parent int(11) DEFAULT NULL, 5. lft int(11) DEFAULT NULL, 6. rgt int(11) DEFAULT NULL, 7. PRIMARY KEY (id) 8. ) 常用到的 SQL 语句:返回完整的树（Retrieving a Full Tree） Java 代码 1. SELECT node.name 2. 3. FROM nested_c

3、ategory node, nested_category parent 4. WHERE node.lft BETWEEN parent.lft AND parent.rgt 5. AND parent.name = Food 6. ORDER BY node.lft 返回某结点的祖谱路径（Retrieving a Single Path） Java 代码 1. SELECT parent.name 2. FROM nested_category node, nested_category parent 3. WHERE node.lft BETWEEN parent.lft AND par

4、ent.rgt 4. AND node.name = Yellow 5. ORDER BY node.lft 返回所有节点的深度（Finding the Depth of the Nodes）提示：所有父结点的个数即为深度,最外面那层 select 是为了对 node.lft 进行排序，而 between and 操作是相当于= and 0 24. ORDER BY T.Lft 返回所有的叶子节点（Finding all the Leaf Nodes）提示：左值与右值相减为 1 的所有结点即为叶子结点Java 代码 1. SELECT name FROM nested_category W

5、HERE rgt = lft + 1 插入节点（Adding New Nodes）即所有的后续的子结点都相应的加 2Java 代码 1. LOCK TABLE nested_category WRITE; 2. SELECT myRight := rgt FROM nested_category WHERE name = TELEVISIONS; 3. UPDATE nested_category SET rgt = rgt + 2 WHERE rgt myRight; 4. UPDATE nested_category SET lft = lft + 2 WHERE lft myRight

6、; 5. INSERT INTO nested_category 6. (name, lft, rgt) 7. VALUES 8. (GAME CONSOLES, myRight + 1, myRight + 2); 9. UNLOCK TABLES; 删除节点（Deleting Nodes）提示：所有后续的结点都相应的减去结点删除后的左右值的偏差Java 代码 1. LOCK TABLE nested_category WRITE; 2. SELECT myLeft := lft, myRight := rgt, myWidth := rgt -lft + 1 3. FROM nested

7、_category 4. WHERE name = GAME CONSOLES; 5. DELETE FROM nested_category WHERE lft BETWEEN myLeft AND myRight; 6. UPDATE nested_category SET rgt = rgt -myWidth WHERE rgt myRight; 7. UPDATE nested_category SET lft = lft -myWidth WHERE lft myRight; 8. UNLOCK TABLES; IntroductionMost users at one time o

8、r another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a pare

9、nt-child relationship that is not naturally represented in a relational database table.For our purposes, hierarchical data is a collection of data where each item has a single parent and zero or more children (with the exception of the root item, which has no parent). Hierarchical data can be found

10、in a variety of database applications, including forum and mailing list threads, business organization charts, content management categories, and product categories. For our purposes we will use the following product category hierarchy from an fictional electronics store:These categories form a hier

11、archy in much the same way as the other examples cited above. In this article we will examine two models for dealing with hierarchical data in MySQL, starting with the traditional adjacency list model.The Adjacency List ModelTypically the example categories shown above will be stored in a table like

12、 the following (Im including full CREATE and INSERT statements so you can follow along):CREATE TABLE category(category_id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(20) NOT NULL,parent INT DEFAULT NULL);INSERT INTO categoryVALUES(1,ELECTRONICS,NULL),(2,TELEVISIONS,1),(3,TUBE,2),(4,LCD,2),(5,PLASMA,

13、2),(6,PORTABLE ELECTRONICS,1),(7,MP3 PLAYERS,6),(8,FLASH,7),(9,CD PLAYERS,6),(10,2 WAY RADIOS,6);SELECT * FROM category ORDER BY category_id;+-+-+-+| category_id | name | parent |+-+-+-+| 1 | ELECTRONICS | NULL | 2 | TELEVISIONS | 1 | 3 | TUBE | 2 | 4 | LCD | 2 | 5 | PLASMA | 2 | 6 | PORTABLE ELECTR

14、ONICS | 1 | 7 | MP3 PLAYERS | 6 | 8 | FLASH | 7 | 9 | CD PLAYERS | 6 | 10 | 2 WAY RADIOS | 6 |+-+-+-+10 rows in set (0.00 sec)In the adjacency list model, each item in the table contains a pointer to its parent. The topmost element, in this case electronics, has a NULL value for its parent. The adja

15、cency list model has the advantage of being quite simple, it is easy to see that FLASH is a child of mp3 players, which is a child of portable electronics, which is a child of electronics. While the adjacency list model can be dealt with fairly easily in client-side code, working with the model can

16、be more problematic in pure SQL.Retrieving a Full TreeThe first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self-join:SELECT t1.name AS lev1, t2.name

17、 as lev2, t3.name as lev3, t4.name as lev4FROM category AS t1LEFT JOIN category AS t2 ON t2.parent = t1.category_idLEFT JOIN category AS t3 ON t3.parent = t2.category_idLEFT JOIN category AS t4 ON t4.parent = t3.category_idWHERE t1.name = ELECTRONICS;+-+-+-+-+| lev1 | lev2 | lev3 | lev4 |+-+-+-+-+|

19、|+-+-+-+-+6 rows in set (0.00 sec)Finding all the Leaf NodesWe can find all the leaf nodes in our tree (those with no children) by using a LEFT JOIN query:SELECT t1.name FROMcategory AS t1 LEFT JOIN category as t2ON t1.category_id = t2.parentWHERE t2.category_id IS NULL;+-+| name |+-+| TUBE | LCD |

20、PLASMA | FLASH | CD PLAYERS | 2 WAY RADIOS |+-+Retrieving a Single PathThe self-join also allows us to see the full path through our hierarchies:SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4FROM category AS t1LEFT JOIN category AS t2 ON t2.parent = t1.category_idLEFT JOIN

21、 category AS t3 ON t3.parent = t2.category_idLEFT JOIN category AS t4 ON t4.parent = t3.category_idWHERE t1.name = ELECTRONICS AND t4.name = FLASH;+-+-+-+-+| lev1 | lev2 | lev3 | lev4 |+-+-+-+-+| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |+-+-+-+-+1 row in set (0.01 sec)The main limit

22、ation of such an approach is that you need one self-join for every level in the hierarchy, and performance will naturally degrade with each level added as the joining grows in complexity.Limitations of the Adjacency List ModelWorking with the adjacency list model in pure SQL can be difficult at best

23、. Before being able to see the full path of a category we have to know the level at which it resides. In addition, special care must be taken when deleting nodes because of the potential for orphaning an entire sub-tree in the process (delete the portable electronics category and all of its children

24、 are orphaned). Some of these limitations can be addressed through the use of client-side code or stored procedures. With a procedural language we can start at the bottom of the tree and iterate upwards to return the full tree or a single path. We can also use procedural programming to delete nodes

25、without orphaning entire sub-trees by promoting one child element and re-ordering the remaining children to point to the new parent.The Nested Set ModelWhat I would like to focus on in this article is a different approach, commonly referred to as the Nested Set Model. In the Nested Set Model, we can

26、 look at our hierarchy in a new way, not as nodes and lines, but as nested containers. Try picturing our electronics categories this way:Notice how our hierarchy is still maintained, as parent categories envelop their children.We represent this form of hierarchy in a table through the use of left an

27、d right values to represent the nesting of our nodes:CREATE TABLE nested_category (category_id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(20) NOT NULL,lft INT NOT NULL,rgt INT NOT NULL);INSERT INTO nested_categoryVALUES(1,ELECTRONICS,1,20),(2,TELEVISIONS,2,9),(3,TUBE,3,4),(4,LCD,5,6),(5,PLASMA,7,8)

28、,(6,PORTABLE ELECTRONICS,10,19),(7,MP3 PLAYERS,11,14),(8,FLASH,12,13),(9,CD PLAYERS,15,16),(10,2 WAY RADIOS,17,18);SELECT * FROM nested_category ORDER BY category_id;+-+-+-+-+| category_id | name | lft | rgt |+-+-+-+-+| 1 | ELECTRONICS | 1 | 20 | 2 | TELEVISIONS | 2 | 9 | 3 | TUBE | 3 | 4 | 4 | LCD

29、| 5 | 6 | 5 | PLASMA | 7 | 8 | 6 | PORTABLE ELECTRONICS | 10 | 19 | 7 | MP3 PLAYERS | 11 | 14 | 8 | FLASH | 12 | 13 | 9 | CD PLAYERS | 15 | 16 | 10 | 2 WAY RADIOS | 17 | 18 |+-+-+-+-+We use lft and rgt because left and right are reserved words in MySQL, see http:/ for the full list of reserved words

30、.So how do we determine left and right values? We start numbering at the leftmost side of the outer node and continue to the right:This design can be applied to a typical tree as well:When working with a tree, we work from left to right, one layer at a time, descending to each nodes children before

31、assigning a right-hand number and moving on to the right. This approach is called the modified preorder tree traversal algorithm.Retrieving a Full TreeWe can retrieve the full tree through the use of a self-join that links parents with nodes on the basis that a nodes lft value will always appear bet

33、S | FLASH | CD PLAYERS | 2 WAY RADIOS |+-+Unlike our previous examples with the adjacency list model, this query will work regardless of the depth of the tree. We do not concern ourselves with the rgt value of the node in our BETWEEN clause because the rgt value will always fall within the same pare

34、nt as the lft values.Finding all the Leaf NodesFinding all leaf nodes in the nested set model even simpler than the LEFT JOIN method used in the adjacency list model. If you look at the nested_category table, you may notice that the lft and rgt values for leaf nodes are consecutive numbers. To find

37、ow the entire tree, but what if we want to also show the depth of each node in the tree, to better identify how each node fits in the hierarchy? This can be done by adding a COUNT function and a GROUP BY clause to our existing query for showing the entire tree:SELECT node.name, (COUNT(parent.name) -

38、 1) AS depthFROM nested_category AS node,nested_category AS parentWHERE node.lft BETWEEN parent.lft AND parent.rgtGROUP BY node.nameORDER BY node.lft;+-+-+| name | depth |+-+-+| ELECTRONICS | 0 | TELEVISIONS | 1 | TUBE | 2 | LCD | 2 | PLASMA | 2 | PORTABLE ELECTRONICS | 1 | MP3 PLAYERS | 2 | FLASH |

39、 3 | CD PLAYERS | 2 | 2 WAY RADIOS | 2 |+-+-+We can use the depth value to indent our category names with the CONCAT and REPEAT string functions:SELECT CONCAT( REPEAT( , COUNT(parent.name) - 1), node.name) AS nameFROM nested_category AS node,nested_category AS parentWHERE node.lft BETWEEN parent.lft

41、our hierarchy. Web developers could loop through the tree, adding and tags as the depth number increases and decreases.Depth of a Sub-TreeWhen we need depth information for a sub-tree, we cannot limit either the node or parent tables in our self-join because it will corrupt our results. Instead, we

42、add a third self-join, along with a sub-query to determine the depth that will be the new starting point for our sub-tree:SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1) AS depthFROM nested_category AS node,nested_category AS parent,nested_category AS sub_parent,(SELECT node.name, (COUN

展开阅读全文

结构树 树表设计 二叉树.doc

结构树树表设计二叉树.doc