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

时间：2022-05-23 02:10:23

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

解决的问题是：

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, '');

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]= ''

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('', Path) = ''

相关文章