逻辑数据库设计 - 单纯的树(多级递归关系数据).doc

逻辑数据库设计 - 单纯的树(递归关系数据)相信有过开发经验的朋友都曾碰到过这样一个需求。假设你正在为一个新闻网站开发一个评论功能，读者可以评论原文甚至相互回复。 　　这个需求并不简单，相互回复会导致无限多的分支，无限多的祖先-后代关系。这是一种典型的递归关系数据。 　　对于这个问题，以下给出几个解决方案，各位客观可斟酌后选择。 一、邻接表:依赖父节点 　　邻接表的方案如下(仅仅说明问题)： 　　CREATE TABLE Comments( 　　　　CommentI　　PK, 　　　　ParentId　　 int,　　　　--记录父节点 　　　　ArticleI, 　　　　CommentBody nvarchar(500), 　　　　FOREIGN KEY (ParentId) REFERENCES Comments(CommentId) 　　--自连接，主键外键都在自己表内 　　　　FOREIGN KEY (ArticleId) REFERENCES Articles(ArticleId) 　　) 　　由于偷懒，所以采用了书本中的图了，Bugs就是Articles： 　　 　　这种设计方式就叫做邻接表。这可能是存储分层结构数据中最普通的方案了。 　　下面给出一些数据来显示一下评论表中的分层结构数据。示例表： 　　 　　图片说明存储结构： 　　 　　邻接表的优缺分析 　　对于以上邻接表，很多程序员已经将其当成默认的解决方案了，但即便是这样，但它在从前还是有存在的问题的。 　　分析1：查询一个节点的所有后代(求子树)怎么查呢？ 　　我们先看看以前查询两层的数据的SQL语句： 　　SELECT c1.*,c2.* 　　FROM Comments c1 LEFT OUTER JOIN Comments2 c2 　　ON c2.ParentId = c1.CommentId 　　显然，每需要查多一层，就需要联结多一次表。SQL查询的联结次数是有限的，因此不能无限深的获取所有的后代。而且，这种这样联结，执行Count()这样的聚合函数也相当困难。 　　说了是以前了，现在什么时代了，在SQLServer 2005之后，一个公用表表达式就搞定了,顺带解决的还有聚合函数的问题(聚合函数如Count()也能够简单实用)，例如查询评论4的所有子节点： WITH COMMENT_CTE(CommentId,ParentId,CommentBody,tLevel) AS ( --基本语句 SELECT CommentId,ParentId,CommentBody,0 AS tLevel FROM Comment WHERE ParentId = 4 UNION ALL --递归语句 SELECT c.CommentId,c.ParentId,c.CommentBody,ce.tLevel + 1 FROM Comment AS c INNER JOIN COMMENT_CTE AS ce --递归查询 ON c.ParentId = ce.CommentId ) SELECT * FROM COMMENT_CTE 　　显示结果如下： 　　 　　那么查询祖先节点树又如何查呢？例如查节点6的所有祖先节点: WITH COMMENT_CTE(CommentId,ParentId,CommentBody,tLevel) AS ( --基本语句 SELECT CommentId,ParentId,CommentBody,0 AS tLevel FROM Comment WHERE CommentId = 6 UNION ALL SELECT c.CommentId,c.ParentId,c.CommentBody,ce.tLevel - 1 FROM Comment AS c INNER JOIN COMMENT_CTE AS ce　　--递归查询 ON ce.ParentId = c.CommentId where ce.CommentId ce.ParentId ) SELECT * FROM COMMENT_CTE ORDER BY CommentId ASC 　　结果如下： 　　 　　再者，由于公用表表达式能够控制递归的深度，因此，你可以简单获得任意层级的子树。 　　OPTION(MAXRECURSION 2) 　　看来哥是为邻接表平反来的。 　　?分析2：当然，邻接表也有其优点的，例如要添加一条记录是非常方便的。 　　INSERT INTO Comment(ArticleId,ParentI