sqlserver另类非递归的无限级分类(存储过程版)

网络上的无限级分类系统，它们看似复杂，但大体上可以被划分为两类解决方案：递归算法和非递归算法。对于这两种方法，我们进一步深入一下其中的细节和优劣。

让我们关注递归算法的应用。在狼蚁网站的SEO优化方案中，递归算法被用于处理分类信息的层级结构。这种方案利用数据库的表结构，如CategoryID、CategoryName、ParentID以及Depth，通过递归调用寻找上级目录。这种方法结构清晰，对于理解和维护层级关系很有帮助。为了更方便地管理分类信息，还可以添加CategoryLeft和CategoryRight字段来保存上级或下级目录信息。

接下来是第二种方案，它通过设置CategoryPath字段来保存目录的完整路径。这种方法将父目录的ID用符号分隔开来，例如“1,5,8,10”。虽然这种方式在获取上级和下级分类信息时较为方便，但它的缺陷在于当分类层级增多或需要进行大量数据操作时，操作的复杂性会增加，甚至可能违反数据库设计的范式。对此，许多开发者选择在新闻表等其它表中额外添加CategoryID和CategoryPath字段以提高查询效率。

今天我们要的算法是第二种方案的改进版。它使用两个表来保存分类信息，一个表保存具体的分类信息，另一个表则保存分类之间的关系。这样做能够更好地处理分类信息的层级关系，同时避免了一些操作上的复杂性。表结构包括CategoryID、分类名称以及Depth等字段。对于添加、编辑和删除操作，虽然初始设置可能稍显复杂，但通过存储过程可以简化这些操作。

添加分类（Category_Add）

在数据的海洋中寻找新分类，犹如在广袤的星空中新星。今天，我们将为您展示一段关于添加新分类的SQL存储过程，带您领略数据库世界的奥秘。

代码片段如下：

```sql

CREATE PROCEDURE [dbo].[Category_Add]

@CategoryName NVARCHAR(50),

@BindCategoryID INT,

@CategoryID INT OUTPUT

AS

BEGIN

-- 设置成功标志为初始状态

DECLARE @Success BIT

SET @Success = 1

-- 生成独一无二的分类ID

DECLARE @i BIT

SET @i = 0

WHILE @i = 0

BEGIN

SET @CategoryID = LEFT(10000000 + CONVERT(BIGINT, ABS(CHECKSUM(NEWID()))), 8)

IF NOT EXISTS (SELECT CategoryID FROM tomi_Category WHERE CategoryID = @CategoryID)

SET @i = 1

END

-- 获取信息

DECLARE @Depth INT

SET @Depth = 0

SELECT @Depth = Depth FROM tomi_Category WHERE CategoryID = @BindCategoryID

SET @Depth = @Depth + 1

BEGIN TRAN

INSERT INTO tomi_Category(categoryID, CategoryName, Depth) VALUES(@CategoryID, @CategoryName, @Depth)

IF @@ERROR <> 0 BEGIN ROLLBACK TRAN SET @Success = 0 END

图片说明： 以下是关于分类编辑、删除、列表显示的代码解读。

一、分类编辑（Category_Edit）功能解读

想象一下你正在操作一个分类编辑的程序。在输入特定的分类ID、分类名称和绑定分类ID后，这个名为Category_Edit的存储过程开始了它的工作。它启动一个事务，确保数据更新的完整性。接着，它会更新数据库中的分类信息。如果更新过程中发生错误，它会回滚事务并返回错误代码。如果一切正常，它会提交事务。除此之外，它还会检测是否更改了上级目录，包括判断上级目录是否存在和更新目录等步骤。如果存在子目录，会进行一系列的更新操作。如果在这个过程中发生任何错误，它将撤销事务并返回错误代码。否则，成功提交事务。这是一个非常详尽的过程，确保数据的准确性和完整性。

二、删除分类（Category_Del）功能解读

删除分类是一个直接且迅速的过程。在输入分类ID后，存储过程开始删除操作。它首先删除与指定分类ID相关的所有子分类信息，然后删除主分类信息。如果在删除过程中发生任何错误，它会撤销事务并返回错误代码。否则，成功提交事务。这是一个简单直接的存储过程，用于确保在删除分类时数据的完整性和一致性。

三、分类列表显示（Category_List）功能解读

此存储过程的主要任务是列出所有的分类信息。它联接主分类表和绑定分类表，根据指定的筛选出符合条件的分类信息，并按顺序排列。执行此过程可以直接显示出特定的分类等级列表。这是一个快速而方便的方式，可以清晰地查看分类信息结构。

这些存储过程设计得既细致又周全，既考虑了数据的更新和删除，又考虑了数据的展示和查询效率，展现出良好的数据管理和操作体验。在实际应用中，它们可以大大提高数据操作的效率和准确性。揭开分类体系的神秘面纱：上级与下级子分类列表的便捷查询

作者：TomiWong

日期：2010年7月18日

首发于博客园，转载请注明链接，网络推广，网站推广。

亲爱的读者们，今天我将向大家介绍两个实用的SQL查询程序，它们能帮助我们快速获取分类的完整子目录集和下级目录。在大数据的时代，这无疑会大大提高我们工作的效率。

一、上级子分类列表 (Category_UpTree)

让我们先来理解一下什么是上级子分类。假设我们有一个分类体系，每个分类都有层级关系，那么上级子分类就是指某个分类的上一级分类及其所有上级分类。现在，让我们通过一段SQL代码来如何获取上级子分类列表。

代码示例：

```sql

Create Proc Category_UpTree @CategoryID int

as

select c.

from tomi_Category c

left join tomi_CategoryBind b on c.CategoryID=b.BindCategoryID

where b.CategoryID=@CategoryID

order by c.Depth

GO

exec Category_UpTree 63919523

```

执行上述代码后，你就可以得到一个分类的完整上级子目录集。只需一条SQL语句，就能轻松获取所需信息，方便快捷。

二、下级子分类列表(Category_downTree)

与上级子分类相反，下级子分类是指某个分类的直接和间接下级分类。以下是获取下级子分类列表的SQL代码。

代码示例：

```sql

Create Proc Category_DownTree @CategoryID int

as

select c.

from tomi_Category c

left join tomi_CategoryBind b on c.CategoryID=b.CategoryID

where b.BindCategoryID=@CategoryID

order by c.Depth

GO

exec Category_DownTree 21779652

```

这条SQL语句能够返回一个分类的完整下级目录。如果你想了解某个分类及其所有下级分类下的产品，使用这个查询就足够方便。

以上代码是初稿，可能还存在一些错误，欢迎大家指正。在使用时请根据实际情况进行调整和优化。

作者：TomiWong

注：本文首发于博客园，网络推广，网站推广，转载请注明来源链接。如有任何疑问，欢迎交流。让我们一起学习进步！