SQL Server 处理树结构数据的一个示例

没多少技术含量，在简单的系统里应用问题不大；

解决的问题是：

1.树结构数据的表设计；

2.对任意一个节点，找到他所有的上级节点

3.对任意一个节点，找到他所有的下级节点

 

这里的部分SQL是同事给的，具体出处不详；废话不多说，直接看例子吧；

 

1. 表设计，以比较常见的组织机构表为例，典型的树结构

create table Orgnization (
   ID                   int                  not null,
   Name                 nvarchar(50)         not null,
   Description          nvarchar(300)        null,
   Leader               nvarchar(50)         null,
   Status               int                  null,
   Parent               int                  not null,
   Path                 varchar(100)         not null,
   constraint PK_ORGNIZATION primary key (ID)
)

其中，Parent表示上级机构的ID，没有上级机构的话，就填0；

需要关注的是Path字段，由所有上级节点ID拼成的字符串，以逗号分隔，如： 0,1,4  ， 4是父，1是爷，0是祖宗，依次类推

2.为方便测试，插入一些数据

INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path])  VALUES(1, 'XXX集团', null, null, null, 0, '0');
INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path])  VALUES(2, '111事业部', null, null, null, 1, '0,1');
INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path])  VALUES(3, '222事业部', null, null, null, 1, '0,1');
INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path])  VALUES(4, '333事业部', null, null, null, 1, '0,1');
INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path])  VALUES(5, 'xxx部', null, null, null, 2, '0,1,2');
INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path])  VALUES(6, 'xxx2部', null, null, null, 2, '0,1,2');
INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path])  VALUES(7, 'XXX3部', null, null, null, 2, '0,1,2');
INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path])  VALUES(8, 'yyy部', null, null, null, 3, '0,1,3');
INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path])  VALUES(9, 'zzz部', null, null, null, 4, '0,1,4');
INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path])  VALUES(10, 'aaa组', null, null, null, 5, '0,1,2,5');
INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path])  VALUES(11, 'aaa2组', null, null, null, 5, '0,1,2,5');
INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path])  VALUES(12, 'aaa3组', null, null, null, 10, '0,1,2,5,10');
INSERT INTO Orgnization([ID], [Name], [Description], [Leader], [Status], [Parent], [Path])  VALUES(13, 'bbb组', null, null, null, 9, '0,1,4,9');

3. 找到所有上级节点

;with f as
(
select ID, Name,Parent,Path from Orgnization where [ID]= '4'
union all
select a.ID,a.Name, a.Parent, a.Path from Orgnization a join f b on a.[ID]=b.[Parent] 
)
select * from f order by Path asc

4.找到所有下级节点

这个相对复杂一点，这里采用的方式通过创建和调用SQL函数解决

4.1 创建一个字符串拆分split函数，用于解析类似0,1,4这种csv格式

/****** Object:  UserDefinedFunction [dbo].[m_split]    Script Date:  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))    
   returns @t table(col varchar(200))    
   as    
     begin    
       while(charindex(@split,@c) <>0)    
         begin    
           insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))    
             set @c = stuff(@c,1,charindex(@split,@c),'')  
            -- SET @c = substring(@c,charindex(' ',@c)+1,len(@c))      
         end    
       insert @t(col) values (@c)    
       return    
 end 
GO

4.2 创建一个用于在Path中匹配节点ID的函数

/****** Object:  UserDefinedFunction [dbo].[GetState]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create  function [dbo].[GetState](@s1 varchar(2000),@s2 varchar(2000)) 
 returns int 
 as 
 begin 
 declare @i int 
 declare @j int 
 declare @k int 
 select @i=count(a.col) from dbo.m_split(@s1,',') a 
 right join ( 
 select * from dbo.m_split(@s2,',')) b  
 on 
 a.col=b.col 
 where a.col is not null 
  
 select @j=count(col) from dbo.m_split(@s1,',') 
 where col is not null 
  
 if(@i=@j) 
 set @k=0 
 else 
 set @k=1 
 return @k 
 end 
  
GO

4.3 执行查询语句

select * from dbo.orgnization where dbo.GetState('4', Path) = '0'