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

chriszg

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


　　由于数据库存储的数据都是以平面方式存储，所以目前大部分论坛和其他程序都是用递归来展现层次数据的，如果分类的层次十分深的话那么使用的递归次数相当可观，对性能的影响也非常大。最近要做一个分类信息的平台就遇到这个问题了，那么如何实现快速的展现分层数据呢？MYSQL 的开发者帮我们想到了一个算法，这个算法目前唯一的问题就是尚未实现分类排序，我们可以通过右值的反向排序实现先入先出的排序。在这里我们需要了解的是如何用 SQL Server 来实现，我们就以省市县数据库为例来实现：
http://www.loveyuki.com/Attach/month_0811/7feaas_TreeNum.gif
如图所示我们将一个树节点的左右各编上号码，就可以看出一些规律，山西的左右值为(8,17)，那么所有左值大于8，右值小于17的节点都是属于山西的子节点。稷山先的左右值为(14,15)，那么他的所有父节点就是左值小于14，右值大于15的节点，怎么样，用这个方法实现的无限级分类性能绝对是顶呱呱的。一次查询就可以查出属于某个节点的数据以及他子节点的数据。这个算是我见过性能最高的无限级分类算法。其他算法跟这个对比基本没有任何优势。
　　我们先建立一个数据表，结构如下图(LID 为左值，RID 为右值，Tree 为节点深度，Name 和 ID 就不多说了，节点的索引和名称)
http://www.loveyuki.com/Attach/month_0811/6evplx_TreeTbl.gif
　　我们可以使用下面的存储过程来获得一个节点和其子节点：

• CREATEPROCEDURECLSP_ZoneSelect
  
• (
  
• 
  @RootINT,
  
• 
  @TreeINT
  
• )
  
• 
  AS
  
• 
  SELECTZ.ID,Z.Tree,Z.Name
  
• 
  FROMCL_ZoneDataASZ,CL_ZoneDataASP
  
• 
  WHEREP.ID=@Root
  
• 
      ANDZ.LID>=P.LIDANDZ.RID<=P.RID
  
• 
  AND(@Tree=0ORZ.Tree<=P.Tree+@Tree)
  
• 
  ORDERBYZ.LIDASC
  
• GO
  

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

• CREATEPROCEDURECLSP_ZoneInsert
  
• (
  
• 
  @RootINT,
  
• 
  @NameNVARCHAR(50)
  
• )
  
• 
  AS
  
• 
  DECLARE@RIDASINT,@NIDASINT,@TreeASINT
  
• 
  
• 
  SET@RID=1
  
• 
  SET@NID=0
  
• 
  SET@Tree=1
  
• 
  
• IF@Root=0
  
• 
  BEGIN
  
• 
  SELECTTOP1@RID=RID+1
  
• 
  FROMCL_CateDataORDERBYRIDDESC
  
• 
  END
  
• 
  ELSE
  
• 
  BEGIN
  
• 
  SELECT@RID=RID,@Tree=Tree+1
  
• 
  FROMCL_ZoneDataWHEREID=@Root
  
• 
  END
  
• 
  
• 
  IF@Root=0OR@RID>1
  
• 
  BEGIN
  
• 
  UPDATECL_ZoneDataSETRID=RID+2WHERERID>=@RID
  
• 
  UPDATECL_ZoneDataSETLID=LID+2WHERELID>@RID
  
• 
  
• 
  INSERTINTOCL_ZoneData(LID,RID,Tree,Name)
  
• 
  VALUES(@RID,@RID+1,@Tree,@Name)
  
• 
  
• 
  SET@NID=SCOPE_IDENTITY()
  
• 
  END
  
• 
  SELECT@NID
  
• GO
  

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

• CREATEPROCEDURECLSP_ZoneDelete
  
• (
  
• 
  @IDINT
  
• )
  
• 
  AS
  
• 
  DECLARE@LIDASINT,@RIDASINT,@WIDASINT,@DIDASINT
  
• 
  SET@DID=0
  
• 
  SELECT@DID=ID,@LID=LID,@RID=RID,@WID=RID-LID+1FROMCL_ZoneDataWHEREID=@ID
  
• IF@DID!=0
  
• 
  BEGIN
  
• 
  DELETEFROMCL_ZoneDataWHERELIDBETWEEN@LIDAND@RID
  
• 
  UPDATECL_ZoneDataSETRID=RID-@WIDWHERERID>@RID
  
• 
  UPDATECL_ZoneDataSETLID=LID-@WIDWHERELID>@RID
  
• 
  END
  
• 
  SELECT@DID
  
• GO
  

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





引用：http://www.loveyuki.com/Article/104/Trackback.ashx