树形结构数据的存储

一、概述

树形结构在国人中的需求很多，比如单位和子单位、人员的上下级管理关系等，一般数据库设计是通过Id,Pid来确定父子关系，但如果要查询某个节点下所有的子节点，可以通过with关键字查询效，具体方法可见这篇文章，改进方法主要有两种：物化路径和左右节点。对于使用物化路径，有通过存储过程实现的，比如 ，最近在网上看了一片文章，主要是对物化路径的方法进行改进，使用存储过程自动修改物化路径，文章可见，具体操作如下：

二、数据库设计

CREATE TABLE [dbo].[Depts](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [FullPath] [nvarchar](50) NULL,
    [HierarchyLevel] [int] NULL,
    [Parent_Id] [int] NULL,
    [SortCode] [int] NULL,
）

主要是添加了两个辅助列FullPath和HierarchyLevel，用于记录的全路径和层级。

三、存储过程

2.1插入记录触发器

USE [PeopleManage]
GO
/****** Object:  Trigger [dbo].[trgDeptInsert]    Script Date: 2015/3/27 11:38:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trgDeptInsert] ON [dbo].[Depts]  FOR INSERT
AS 
BEGIN
    DECLARE @numrows int
    SET @numrows = @@ROWCOUNT
    
    if @numrows > 1 
    BEGIN
        RAISERROR('Only single row insertion is supported', 16, 1)
        ROLLBACK TRAN
    END
    ELSE    
    BEGIN
        UPDATE 
            E
        SET
            HierarchyLevel    = 
            CASE 
                WHEN E.Parent_Id IS NULL THEN 0
                ELSE Parent.HierarchyLevel + 1
            END,
            FullPath = 
            CASE
                WHEN E.Parent_Id IS NULL THEN '.'
                ELSE Parent.FullPath 
            END + CAST(E.Id AS varchar(10)) + '.'
            FROM
                Depts AS E
            INNER JOIN
                inserted AS I ON I.Id = E.Id
            LEFT OUTER JOIN
                Depts AS Parent ON Parent.Id = E.Parent_Id
    END
END

2.2修改记录触发器

USE [PeopleManage]
GO
/****** Object:  Trigger [dbo].[trgDeptUpdate]    Script Date: 2015/3/27 11:39:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trgDeptUpdate] ON [dbo].[Depts] FOR UPDATE
AS 
BEGIN
  IF @@ROWCOUNT = 0 
        RETURN
    
    if UPDATE(Parent_Id) 
    BEGIN
        UPDATE
            E
        SET
            HierarchyLevel    = 
                E.HierarchyLevel - I.HierarchyLevel + 
                    CASE 
                        WHEN I.Parent_Id IS NULL THEN 0
                        ELSE Parent.HierarchyLevel + 1
                    END,
            FullPath = 
                ISNULL(Parent.FullPath, '.') +
                CAST(I.Id as varchar(10)) + '.' +
                RIGHT(E.FullPath, len(E.FullPath) - len(I.FullPath))
            FROM
                Depts AS E
            INNER JOIN
                inserted AS I ON E.FullPath LIKE I.FullPath + '%'
            LEFT OUTER JOIN
                Depts AS Parent ON I.Parent_Id = Parent.Id
    END

END

四、使用方法

对于数据的增删改，不需要理会FullPath和HierarchyLevel这两列，触发器会自动添加或者修改

如果要查询某个节点下的所有节点，只需要使用Linke语句即可