一、概述
树形结构在国人中的需求很多，比如单位和子单位、人员的上下级管理关系等，一般数据库设计是通过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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[trgDeptInsert] ON [dbo].[Depts] FOR INSERTAS 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 ENDEND 2.2修改记录触发器
USE [PeopleManage]GO/****** Object: Trigger [dbo].[trgDeptUpdate] Script Date: 2015/3/27 11:39:48 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[trgDeptUpdate] ON [dbo].[Depts] FOR UPDATEAS 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 ENDEND
四、使用方法
对于数据的增删改，不需要理会FullPath和HierarchyLevel这两列，触发器会自动添加或者修改
如果要查询某个节点下的所有节点，只需要使用Linke语句即可
