资源描述:
《结构树 树表设计 二叉树》由会员上传分享,免费在线阅读,更多相关内容在行业资料-天天文库。
1、TheNestedModel树形结构先把树按水平的方式摆开,如下图 然后按前序遍历的方法,为每一个结点编号,每一个结点都有左、右的编号,根结点从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 TABLE `n
2、ested_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. ) CREATETABLE`nested_category`(`id`int(11)NOTNULLAUTO_INCREMENT,
3、`name`varchar(16)DEFAULTNULL,`parent`int(11)DEFAULTNULL,`lft`int(11)DEFAULTNULL,`rgt`int(11)DEFAULTNULL,PRIMARYKEY(`id`)) 常用到的SQL语句:返回完整的树(RetrievingaFullTree)Java代码1.SELECT node.name 2. 3. FROM nested_category node, nested_category parent 4. WHERE node.lft BETWEEN parent.lft AND pare
4、nt.rgt 5. AND parent.name = 'Food' 6. ORDER BY node.lft SELECTnode.nameFROMnested_categorynode,nested_categoryparentWHEREnode.lftBETWEENparent.lftANDparent.rgtANDparent.name='Food'ORDERBYnode.lft返回某结点的祖谱路径(RetrievingaSinglePath)Java代码1.SELECT parent.name 2. FROM nested_category node
5、, nested_category parent 3. WHERE node.lft BETWEEN parent.lft AND parent.rgt 4. AND node.name = 'Yellow' 5. ORDER BY node.lft SELECTparent.nameFROMnested_categorynode,nested_categoryparentWHEREnode.lftBETWEENparent.lftANDparent.rgtANDnode.name='Yellow'ORDERBYnode.lft返回所有节点的深度(Finding
6、theDepthoftheNodes)提示:所有父结点的个数即为深度,最外面那层select是为了对node.lft进行排序,而betweenand操作是相当于>=and<=,所以会包括结点本身,所以要减一Java代码1.SELECT V.* 2. FROM (SELECT node.name, (COUNT(parent.name) - 1) depth 3. FROM nested_category node, nested_category parent 1. WHERE node.lft BETWEEN parent.lft
7、 AND parent.rgt 2. GROUP BY node.name) V, 3. nested_category T 4. WHERE V.name = T.name 5. ORDER BY T.Lft SELECTV.*FROM(SELECTnode.name,(COUNT(parent.name)-1)depthFROMnested_categorynode,nested_categoryparentWHEREnode.lftBETWEENparent.lftAND