MSSQL实现树形结构管理的技巧-猿码集

1. 概述

树形结构是数据管理系统中非常常见的一种数据结构，它能够很好地表示层次化关系。在MSSQL中实现树形结构管理，主要是通过递归查询和存储过程，来实现根据不同的需求进行数据的增删改查。本文将通过实例介绍MSSQL实现树形结构管理的技巧。

2. 树形结构管理的基本操作

2.1 增加节点

在树形结构管理中，增加一个节点主要涉及到以下几个步骤：

确定新增节点的父节点

生成新节点的唯一标识

将新增节点插入到相应的位置

对于第一步，只需要确定新增节点的父节点即可。对于第二步，可以通过数据库自增量来生成唯一标识，也可以通过GUID等方式生成唯一标识。对于第三步，可以通过递归查询来找到新增节点应该插入的位置，然后使用INSERT语句将节点插入到相应位置。以下是一个示例：


CREATE PROCEDURE dbo.AddNode 
(
  @ParentID INT,
  @NodeName VARCHAR(50)
)
AS
BEGIN
  DECLARE @NewID INT
  SET @NewID = (SELECT ISNULL(MAX(NodeID),0)+1 FROM tb_tree)
  DECLARE @NewLeft INT
  DECLARE @ParentLeft INT
  DECLARE @ParentRight INT
  SELECT @ParentLeft = LeftNum, @ParentRight = RightNum FROM tb_tree WHERE NodeID=@ParentID 
  UPDATE tb_tree SET RightNum = RightNum + 2 WHERE RightNum > @ParentRight
  UPDATE tb_tree SET LeftNum = LeftNum + 2 WHERE LeftNum > @ParentRight
  SET @NewLeft = @ParentRight
  INSERT INTO tb_tree(NodeID,NodeName,LeftNum,RightNum)
  VALUES (@NewID,@NodeName,@NewLeft,@NewLeft+1)
END

2.2 删除节点

在树形结构管理中，删除一个节点主要涉及到以下几个步骤：

删除该节点及其子节点

更新其父节点的左右节点

对于第一步，可以利用递归删除方法，将该节点及其子节点删除。对于第二步，需要更新其父节点的左右节点。以下是一个示例：


CREATE PROCEDURE dbo.DelNode 
(
  @NodeID INT
)
AS
BEGIN
  DECLARE @Left INT
  DECLARE @Right INT
  SELECT @Left=LeftNum,@Right=RightNum FROM tb_tree WHERE NodeID=@NodeID
  DECLARE @Length INT
  SET @Length = @Right - @Left + 1
  DELETE FROM tb_tree WHERE LeftNum BETWEEN @Left AND @Right
  UPDATE tb_tree SET LeftNum = CASE 
                    WHEN LeftNum > @Right THEN LeftNum - @Length 
                    ELSE LeftNum END,
     RightNum = CASE 
                      WHEN RightNum > @Right THEN RightNum - @Length 
                      ELSE RightNum END
  WHERE RightNum > @Left
END

2.3 修改节点

在树形结构管理中，修改一个节点主要涉及到更新节点的属性值。这个主要由UPDATE语句实现。以下是一个示例：


CREATE PROCEDURE dbo.UpdateNode 
(
  @NodeID INT,
  @NewName VARCHAR(50)
)
AS
BEGIN
  UPDATE tb_tree SET NodeName = @NewName WHERE NodeID=@NodeID
END

3. 树形结构的查询

3.1 查询所有子节点

在树形结构管理中，查询某个节点的所有子节点，可以通过递归查询实现。以下是一个示例：


CREATE PROCEDURE dbo.GetAllChildNode 
(
  @NodeID INT
)
AS
BEGIN
  WITH T1 (NodeID,NodeName,LeftNum,RightNum) AS
  (
      SELECT NodeID,NodeName,LeftNum,RightNum FROM tb_tree WHERE NodeID=@NodeID
      UNION ALL
      SELECT B.NodeID,B.NodeName,B.LeftNum,B.RightNum
      FROM tb_tree B,T1
      WHERE B.LeftNum > T1.LeftNum AND B.RightNum < T1.RightNum
  )
  SELECT * FROM T1
END

3.2 查询某一层级的所有节点

在树形结构管理中，查询某一层级的所有节点，可以通过比较左右节点关系和所需的层级关系来实现。以下是一个示例：


CREATE PROCEDURE dbo.GetNodeByLevel 
(
  @Level INT
)
AS
BEGIN
  SELECT * FROM tb_tree WHERE LeftNum-1 IN
  (
      SELECT LeftNum FROM tb_tree WHERE RightNum-LeftNum=1 AND Level = @Level
  )
END

4. 树形结构的批量操作

4.1 批量添加

对于树形结构的批量添加，可以通过封装INSERT语句为存储过程来实现。以下是一个示例：


CREATE PROCEDURE dbo.BatchAddNode 
(
    @ParentID INT,
    @NodeList XML
)
AS
BEGIN
    DECLARE @Nodes TABLE(NodeName VARCHAR(50))
    INSERT INTO @Nodes(NodeName)
    SELECT Tbl.Col.value('.','VARCHAR(50)') AS NodeName FROM @NodeList.nodes('/Nodes/Node') AS Tbl(Col)
    DECLARE @RowCount INT
    SET @RowCount = @@ROWCOUNT
    DECLARE @NewID INT
    SET @NewID = (SELECT ISNULL(MAX(NodeID),0)+1 FROM tb_tree)
    DECLARE @NewLeft INT
    DECLARE @ParentLeft INT
    DECLARE @ParentRight INT
    SELECT @ParentLeft = LeftNum, @ParentRight = RightNum FROM tb_tree WHERE NodeID=@ParentID 
    UPDATE tb_tree SET RightNum = RightNum + (2 * @RowCount) WHERE RightNum > @ParentRight
    UPDATE tb_tree SET LeftNum = LeftNum + (2 * @RowCount) WHERE LeftNum > @ParentRight
    SET @NewLeft = @ParentRight
    DECLARE @xxml XML=''
    DECLARE @count INT=1
    WHILE(@count<=@RowCount)
    BEGIN
        DECLARE @nodename VARCHAR(50)
        SELECT @nodename=NodeName FROM @Nodes WHERE ID=@count
        SET @xxml.modify('insert  into (/Rows)[1]')
        SET @count=@count+1
    END
    INSERT INTO tb_tree(NodeID,NodeName,LeftNum,RightNum)
    SELECT ID,Name,LeftNum,LeftNum+1 FROM
    (
      SELECT @NewID+ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 AS ID,
      Tbl.Col.value('@Name','VARCHAR(50)') AS Name,
      @NewLeft+ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*2-2 AS LeftNum
      FROM @xxml.nodes('/Rows/Row') AS Tbl(Col)
    ) A
END

4.2 批量修改

对于树形结构的批量修改，可以通过封装UPDATE语句为存储过程来实现。以下是一个示例：


CREATE PROCEDURE dbo.BatchUpdateNode 
(
  @NodeList XML
)
AS
BEGIN
  UPDATE tb_tree SET NodeName = Tbl.Col.value('@NewName','VARCHAR(50)') 
  FROM tb_tree,Tbl.Col.value('@NodeID','INT') AS PNodeID
  WHERE tb_tree.NodeID = PNodeID
END

4.3 批量删除

对于树形结构的批量删除，可以通过递归删除方法，将该节点及其子节点删除。以下是一个示例：


CREATE PROCEDURE dbo.BatchDelNode 
(
  @NodeList XML
)
AS
BEGIN
  DECLARE @TbNodes TABLE(NodeID INT)
  INSERT INTO @TbNodes(NodeID)
  SELECT Tbl.Col.value('.','INT') AS NodeID FROM @NodeList.nodes('/Nodes/Node') AS Tbl(Col)
  DECLARE @RowCount INT
  SET @RowCount = @@ROWCOUNT
  DECLARE @Left INT
  DECLARE @Right INT
  DECLARE @count INT = 1
  WHILE(@count<=@RowCount)
  BEGIN
    SELECT @Left=LeftNum,@Right=RightNum FROM tb_tree WHERE NodeID IN (SELECT NodeID FROM @TbNodes WHERE NodeID=@count)
    DECLARE @Length INT
    SET @Length = @Right - @Left + 1
    DELETE FROM tb_tree WHERE LeftNum BETWEEN @Left AND @Right
    UPDATE tb_tree SET LeftNum = CASE 
                    WHEN LeftNum > @Right THEN LeftNum - @Length 
                    ELSE LeftNum END,
     RightNum = CASE 
                      WHEN RightNum > @Right THEN RightNum - @Length 
                      ELSE RightNum END
  WHERE RightNum > @Left
      SET @count=@count+1
  END
END

5. 总结

本文从树形结构管理的基本操作、树形结构的查询以及树形结构的批量操作三个方面详细介绍了如何在MSSQL中实现树形结构管理。通过掌握本文所述的技巧，相信读者能够很好地应用树形结构进行数据的管理操作。

MSSQL实现树形结构管理的技巧