SQL Server 存储层级数据实现无限级分类

由于数据库存储的数据都是以平面方式存储，所以目前大部分论坛和其他程序都是用递归来展现层次数据的，如果分类的层次十分深的话那么使用的递归次数相当可观，对性能的影响也非常大。最近要做一个分类信息的平台就遇到这个问题了，那么如何实现快速的展现分层数据呢？MYSQL 的开发者帮我们想到了一个算法，这个算法目前唯一的问题就是尚未实现分类排序，我们可以通过右值的反向排序实现先入先出的排序。在这里我们需要了解的是如何用 SQL Server 来实现，我们就以省市县数据库为例来实现：

如图所示我们将一个树节点的左右各编上号码，就可以看出一些规律，山西的左右值为(8,17)，那么所有左值大于8，右值小于17的节点都是属于山西的子节点。稷山先的左右值为(14,15)，那么他的所有父节点就是左值小于14，右值大于15的节点，怎么样，用这个方法实现的无限级分类性能绝对是顶呱呱的。一次查询就可以查出属于某个节点的数据以及他子节点的数据。这个算是我见过性能最高的无限级分类算法。其他算法跟这个对比基本没有任何优势。

我们先建立一个数据表，结构如下图(LID 为左值，RID 为右值，Tree 为节点深度，Name 和 ID 就不多说了，节点的索引和名称)

我们可以使用下面的存储过程来获得一个节点和其子节点：

CREATE PROCEDURE CLSP_ZoneSelect  
(  
    @Root INT,  
    @Tree INT 
)  
AS 
    SELECT Z.ID,Z.Tree,Z.Name  
    FROM CL_ZoneData AS Z,CL_ZoneData AS P  
    WHERE   P.ID = @Root  
            AND Z.LID >= P.LID AND Z.RID <= P.RID  
            AND (@Tree = 0 OR Z.Tree <= P.Tree + @Tree)  
    ORDER BY Z.LID ASC 
GO

我们可以用下面这个存储过程来在一个节点下插入新的子节点：

CREATE PROCEDURE CLSP_ZoneInsert  
(  
    @Root INT,  
    @Name NVARCHAR(50)  
)  
AS 
    DECLARE @RID AS INT,@NID AS INT,@Tree AS INT 
 
    SET @RID = 1  
    SET @NID = 0  
    SET @Tree = 1  
 
    IF @Root = 0  
    BEGIN 
        SELECT TOP 1 @RID = RID + 1  
        FROM CL_CateData ORDER BY RID DESC 
    END 
    ELSE 
    BEGIN 
        SELECT @RID = RID, @Tree = Tree + 1  
        FROM CL_ZoneData WHERE ID = @Root  
    END 
 
    IF @Root = 0 OR @RID > 1  
    BEGIN 
        UPDATE CL_ZoneData SET RID = RID + 2 WHERE RID >= @RID  
        UPDATE CL_ZoneData SET LID = LID + 2 WHERE LID > @RID  
 
        INSERT INTO CL_ZoneData(LID,RID,Tree,Name)  
        VALUES (@RID,@RID + 1,@Tree,@Name)  
 
        SET @NID = SCOPE_IDENTITY()  
    END 
    SELECT @NID  
GO

删除一个节点可以用下面的存储过程：

CREATE PROCEDURE CLSP_ZoneDelete  
(  
    @ID INT 
)   
AS 
    DECLARE @LID AS INT, @RID AS INT, @WID AS INT, @DID AS INT 
    SET @DID = 0  
    SELECT @DID = ID, @LID = LID, @RID = RID, @WID = RID - LID + 1 FROM CL_ZoneData WHERE ID = @ID  
    IF @DID != 0  
    BEGIN 
        DELETE FROM CL_ZoneData WHERE LID BETWEEN @LID AND @RID  
        UPDATE CL_ZoneData SET RID = RID - @WID WHERE RID > @RID  
        UPDATE CL_ZoneData SET LID = LID - @WID WHERE LID > @RID  
    END 
    SELECT @DID  
GO

此文章由 Loveyuki 于 2008-11-14 18:11 编辑

本日志由 Loveyuki 于 2008-11-09 17:51 发表，目前已经被浏览 4441 次，评论 18 次；

作者添加了以下标签： SQL Server，层级数据，无限级分类；

引用通告：http://blog.loveyuki.com/Article/104/Trackback.ashx

评论订阅：http://blog.loveyuki.com/Article/104/Feeds.ashx

SQL Server 的 DBCC CHECKDB 的使用2009-08-20 12:07
SQL Server 2005+ 使用 CLR 来分割字符串2010-06-03 11:33
SQL Server 2005+ 使用 CTE 分割字符串2010-05-28 16:15
SQL Server 2008 R2 将数据导出为脚本2010-07-14 09:25
把 SQL Server 存储的层级数据读出成树显示2008-11-15 18:43

评论列表

落日惊鸿

2008-11-09 18:51 | # | 回复

老大，大佬，你现在使用的SKIN能不能发出来一下呀？
Lwy

2008-11-09 19:25 | # | 回复

楼上的，自己动手，丰衣足食
Fufu

2008-11-10 13:46 | # | 回复

页面中显示的代码看起来排得很好，也高亮，复制下来使用时变作一团，很多博客使用ul li可以很好的处理。
Loveyuki

2008-11-10 15:28 | # | 回复

嗯，我发现复制下来以后没有回车了。很奇怪啊。Bitrac 也是用的 ul，li。
阎河

2008-11-10 19:32 | # | 回复

感激涕零！
RainBlog

2008-11-11 17:21 | # | 回复

Bitrac 用的什么数据库？？？？
Lwy

2008-11-11 18:05 | # | 回复

@RainBlog 用的是Sqlite数据库
吕游

2008-11-11 18:35 | # | 回复

落叶知秋可以和贵站做连接吗？
www.luckylvyou.com
方便的话去我站串门。提交连接。。
我的基本都是原创字眼，开博2年
Taihom

2008-11-12 09:01 | # | 回复

楼主，我这两天也在写这个算法的实现，用存储过程。希望可以跟你交流，我的QQ447553 如果加我注明MPTT交流。
RainBlog

2008-11-13 18:09 | # | 回复

Sqlite数据库是windows里面内置的吗？？？是不是所有的服务器都支持呢？？？
我佛山人

2008-11-16 22:10 | # | 回复

这个算法在节点移动时会比较麻烦
Jeffer

2008-11-17 09:54 | # | 回复

节点排序已经可以了。节点移动比较麻烦，不过可以曲线救国，在需要移动到的节点建立一个新的。然后把子节点和数据都移动过去，再删除现有的节点。
Huan.Zuo

2008-11-18 04:54 | # | 回复

这算法让我想起了数学系的那帮家伙了。。。儿子和父亲的关系永远都那么模糊。
Huan.Zuo

2008-11-18 04:57 | # | 回复

补充：二岔树和递归真的真的有点模糊了。。。
牛B吹的呱呱叫

2008-11-26 15:33 | # | 回复

实际效果自己测试过么？
还没想到

2008-12-12 08:56 | # | 回复

这个算法添加删除都已经搞好，只是显示的时候有点麻烦，我准备是所有数据一次读入数组再进行处理，处理这部分不知道怎么组合了，最终组合出来能够分清父与子的关系。。。。
Loveyuki

2008-12-17 20:12 | # | 回复

我是直接读出成列表，不过增加了一个深度的字段，通过深度的字段来进行判断是否下一层节点。
Loveyuki

2010-03-18 15:24 | # | 回复

经过两天的画画写写，终于实现了分类的转移，排序提升。呵呵