一张表保存一个无限级树形目录

首页 > 代码库 > 一张表保存一个无限级树形目录

一张表保存一个无限级树形目录

2024-07-18 14:55:12 221人阅读

一张表保存一个无限级树形目录：

/****** Object:  Table [dbo].[TreeMenue]    Script Date: 08/20/2014 18:03:00 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[TreeMenue](    [ID] [int] IDENTITY(1,1) NOT NULL,    [MenueName] [varchar](50) NULL,    [ParentID] [int] NULL,    [URL] [varchar](500) NULL, CONSTRAINT [PK_TreeMenue] PRIMARY KEY CLUSTERED (    [ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[TreeMenue]  WITH NOCHECK ADD  CONSTRAINT [FK_TreeMenue_TreeMenue] FOREIGN KEY([ParentID])REFERENCES [dbo].[TreeMenue] ([ID])GOALTER TABLE [dbo].[TreeMenue] NOCHECK CONSTRAINT [FK_TreeMenue_TreeMenue]GO

View Code

读取树形目录的SQL

with c as (     select * from TreeMenue where [Id] = 1     union all     select a.* from TreeMenue as a     join c on a.ParentId = c.Id ) select * from c

View Code

在SQL Server 2008中提供了一种新的数据类型hierarchyid和相关的操作方法来存储和查询这种树型层次关系数据

以下来自转载：

本文原创自CSDN TJVictor专栏：http://blog.csdn.net/tjvictor/archive/2009/07/30/4395681.aspx

使用SQL Server 2008中的hierarchyid类型来设计具有树型层次关系的表

本文主要讲述三个内容：
1.如何创建hierarychyid的表，插入数据及基本递归查询。
2.介绍hierarchyid的10种专有函数。
3.介绍hierarchyid特有的深度优先索引(Depth-First Indexing)和广度优先索引(Breadth-First Indexing)

在上一节中

http://blog.csdn.net/tjvictor/archive/2009/07/30/4395677.aspx
我们已经演示了如何在SQL Server中通过主键和外键来存储如下图所示的树型结构数据

虽然通过主键和外键的相互搭配可以满足我们的查询、存储需求，但是这种方式并不易于管理和维护，幸运的是，在SQL Server 2008中提供了一种新的数据类型hierarchyid和相关的操作方法来存储和查询这种树型层次关系数据。

首先创建数据表:
create database TestDb
go
use TestDb
go
Create table EmployeeTreeTable
(
NodeId        hierarchyid PRIMARY KEY,
NodeLevel     AS NodeId.GetLevel(),
EmployeeId    int UNIQUE NOT NULL,
EmployeeName nvarchar(32) NOT NULL,
)
NodeId是记录树型层次的Id，是hierarchyid类型。NodeLevel是个计算列，用于存储当前树是深度值，根节点为0。关于NodeId.GetLevel()方法将在下面章节中详细介绍。

按照上图所示的层次关系为表插入数据：
--插入数据
declare @DepthNode hierarchyid;--深度Id
declare @BreadthNode hierarchyid;--广度Id
--插入根节点
insert into EmployeeTreeTable values(hierarchyid::GetRoot(),1,‘项目经理‘)
--计算深度并插入子节点2
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 1;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),2,‘技术经理‘);
--计算节点2广度，在节点2右边插入节点3
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 2;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),3,‘产品经理‘);
--计算节点3广度，在节点3右边插入节点4
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 3;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),4,‘测试经理‘);
--计算节点2深度并插入子节点5
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 2;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),5,‘技术组长1‘);
--计算节点5广度，在节点5右边插入节点6
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 5;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),6,‘技术组长2‘);
--计算节点4深度并插入子节点7
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 4;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),7,‘测试员工1‘);
--计算节点5深度并插入子节点8
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 5;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),8,‘技术员工1‘);
--计算节点8广度，在节点8右边插入节点9
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 8;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),9,‘技术员工2‘);
--计算节点9广度，在节点9右边插入节点10
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 9;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),10,‘技术员工3‘);
go
select * from EmployeeTreeTable
结果集为：
NodeId    NodeLevel    EmployeeId   EmployeeName
0x           0                   1                    项目经理
0x58       1                   2                    技术经理
0x5AC0   2                   5                    技术组长1
0x5AD6   3                   8                    技术员工1
0x5ADA   3                   9                    技术员工2
0x5ADE   3                   10                  技术员工3
0x5B40   2                   6                    技术组长2
0x68       1                   3                    产品经理
0x78       1                   4                    测试经理
0x7AC0   2                   7                    测试员工1

1.查询技术组长1所有子节点的员工信息
select * from EmployeeTreeTable
where NodeId.IsDescendantOf(0x5AC0)=1--0x5AC0是技术组长1的NodeId

2.查询技术组长1所有父节点的员工信息
with c as
(
    select * from EmployeeTreeTable where EmployeeId = 5
    union all
    select a.* from EmployeeTreeTable as a
    join c on a.NodeId = c.NodeId.GetAncestor(1)
)
select * from c

上面的例子中，使用了很多hierarchyid专有的函数，可能大家还不熟悉，下面我将具体介绍一下hierarchyid的10个函数，分别为：
GetRoot，GetLevel，GetAncestor，GetDescendant，IsDescendantOf，ToString，Parse，GetReparentedValue，Read，Write。
1.GetRoot。返回层次结构树的根节点。注意GetRoot() 是静态方法。
关于SQL中静态方法和实例方法的区别请参见：http://blog.csdn.net/tjvictor/archive/2009/07/29/4390673.aspx
SQL:select * from EmployeeTreeTable where NodeId = hierarchyid::GetRoot()
结果集：
NodeId NodeLevel EmployeeId EmployeeName
0x 0 1 项目经理

2.返回一个表示节点在树中的深度的整数。
前面建表时我们已经使用了这个函数，NodeLevel字段就是用这个函数自动创建的。
SQL：select EmployeeName,NodeId.GetLevel() as TreeLevel from EmployeeTreeTable
结果集为：
EmployeeName    TreeLevel
项目经理                0
技术经理                1
技术组长1              2
技术员工1              3
技术员工2              3
技术员工3              3
技术组长2              2
产品经理                1
测试经理                1
测试员工1              2

3.GetAncestor返回表示本节点为的第 n 个父节点的 hierarchyid。
SQL:
declare @NodeId hierarchyid
select @NodeId=NodeId from EmployeeTreeTable where EmployeeId = 5
select EmployeeName,NodeLevel from EmployeeTreeTable where NodeId = @NodeId.GetAncestor(0)
select EmployeeName,NodeLevel from EmployeeTreeTable where NodeId = @NodeId.GetAncestor(1)
select EmployeeName,NodeLevel from EmployeeTreeTable where NodeId = @NodeId.GetAncestor(2)
结果集为：
EmployeeName    NodeLevel
技术组长1              2
技术经理                1
项目经理                0
@NodeId.GetAncestor(0) 取自己节点的Id，@NodeId.GetAncestor(1)取父节点的Id,@NodeId.GetAncestor(2)取爷节点的Id，以此类推。

4.GetDescendant返回父级的一个子节点

如果父级为 NULL，则返回 NULL。
如果父级不为 NULL，而 child1 和 child2 为 NULL，则返回父级的子级。
如果父级和 child1 不为 NULL，而 child2 为 NULL，则返回一个大于 child1 的父级的子级。
如果父级和 child2 不为 NULL，而 child1 为 NULL，则返回一个小于 child2 的父级的子级。
如果父级、child1 和 child2 都不为 NULL，则返回一个大于 child1 且小于 child2 的父级的子级。
如果 child1 不为 NULL 且不是父级的子级，则引发异常。
如果 child2 不为 NULL 且不是父级的子级，则引发异常。
如果 child1 >= child2，则引发异常。
我们在插入的SQL语句中已经使用过了这个方法，这里就不再给出SQL示例，请大家参考前面的插入SQL语句。
5.IsDescendantOf如果子节点为本节点的后代，则返回 true
SQL:select * from EmployeeTreeTable where NodeId.IsDescendantOf(0x58)=1
结果集为:
NodeId    NodeLevel    EmployeeId    EmployeeName
0x58        1                  2                  技术经理
0x5AC0    2                  5                  技术组长1
0x5AD6    3                  8                  技术员工1
0x5ADA    3                  9                  技术员工2
0x5ADE    3                  10                技术员工3
0x5B40    2                  6                  技术组长2
6.ToString返回具有本节点逻辑表示形式的字符串
SQL：select *,NodeId.ToString() as Path from EmployeeTreeTable
结果集为:
NodeId    NodeLevel    EmployeeId    EmployeeName    Path
0x            0                  1                     项目经理                /
0x58        1                  2                    技术经理               /1/
0x5AC0    2                  5                     技术组长1             /1/1/
0x5AD6    3                  8                     技术员工1             /1/1/1/
0x5ADA    3                 9                     技术员工2             /1/1/2/
0x5ADE    3                  10                   技术员工3             /1/1/3/
0x5B40    2                 6                     技术组长2             /1/2/
0x68        1                 3                     产品经理               /2/
0x78        1                 4                     测试经理               /3/
0x7AC0    2                 7                    测试员工1             /3/1/
7.Parse将hierarchyid 的规范字符串表示形式转换为hierarchyid值。即与ToString()函数是相反函数。Parse是静态函数。
SQL：
declare @Path varchar(32) = ‘/1/2/5/6/‘
select hierarchyid::Parse(@Path)
结果集为:0x5B6394
8.GetReparentedValue把当前节点从旧路径更新到新路径
下面的SQL是把技术员工3，从技术组长1节点更新到技术组长2下面。
SQL:
declare @OldNode hierarchyid=0x5AC0;
declare @NewNode hierarchyid=0x5B40;
update EmployeeTreeTable set NodeId = NodeId.GetReparentedValue(@OldNode,@NewNode)
    where EmployeeId = 10
结果集中技术员工3的路径从/1/1/3/变成了/1/2/3/。
关于GetReparentedValue的用法比较复杂，我在介绍索引后，会更加详细的说明各种替换情况。
9.Read和Write
Read和Write是供CLS调用的，不能在T-SQL中直接使用。所以这里就不具体介绍两个函数的使用方法了。

hierarchyid有深度优先索引和广度优先索引
当递归查询父子节点时，会利用到深度优先索引；当平行查询兄弟节点时，会利用到广度优先索引。
深度优先索引图：

广度优先索引图：

1.建立深度优先索引:
深度优先索引是hierarchyid默认的索引，只要在hierarchyid列上建立主键，那么就会自动建立hierarchyid索引。
2.建立广度优先索引
广度优先索引必须是个唯一索引且包括NodeLevel和NodeId两列:
CREATE UNIQUE INDEX IX_EmployeeBreadth ON Employee(NodeLevel, NodeId)
需要注意的是采用深度优先、广度优先还是结合使用这两种索引，以及将哪一种设为聚集键（如果有），取决于上述两种查询类型的相对重要性以及 SELECT 与 DML 操作的相对重要性，本文不代表一定要如此建立hierarchyid索引。

最后我们讨论一下hierarchyid的GetReparentedValue几种使用方法。
下面我们先看一个有问题的节点更新:把技术组长1从技术经理更新到产品经理。
SQL:
declare @OldNode hierarchyid=0x58;
declare @NewNode hierarchyid=0x68;
update EmployeeTreeTable set NodeId = NodeId.GetReparentedValue(@OldNode,@NewNode)
    where EmployeeId = 5
go
select NodeId.ToString(),* from EmployeeTreeTable
结果集为：
路径               NodeId      NodeLevel    EmployeeId    EmployeeName
/                    0x             0                    1                    项目经理
/1/                 0x58         1                    2                    技术经理
/1/1/1/           0x5AD6    3                    8                    技术员工1
/1/1/2/           0x5ADA    3                    9                    技术员工2
/1/1/3/           0x5ADE    3                    10                  技术员工3
/1/2/              0x5B40     2                    6                    技术组长2
/2/                 0x68         1                    3                    产品经理
/2/1/              0x6AC0     2                    5                    技术组长1
/3/                 0x78         1                    4                    测试经理
/3/1/              0x7AC0     2                    7                    测试员工1
从结果里面可以看到技术组长已经变成了/2/1，成功更新到产品经理节点下。但是技术组长1下面的子节点技术员工1，2，3却没有相应的更新过来，还是原来的/1/1/1，2，3，但是原先的技术组长1的/1/1节点已经没有了，所以出现了所谓的“断层”现象。
下面提出几种常用更新需求，并且给出相应的SQL实现语句。
1.职位变更。例如技术经理与产品经理职位互换。
针对这种情况，有两种方法。一是把技术经理下面的所有节点Id都更新成产品经理节点下。这种情况变动比较大，不推荐使用。第二种方法是把技术经理的NodeId和产品经理的NodeId互换。下面使用第二种方法：
declare @TechNode hierarchyid=0x58;
declare @ProductNode hierarchyid=0x68;
declare @TempNode hierarchyid=0x59;
update EmployeeTreeTable set NodeId = @TempNode where NodeId = @TechNode;
update EmployeeTreeTable set NodeId = @TechNode where NodeId = @ProductNode;
update EmployeeTreeTable set NodeId = @ProductNode where NodeId = @TempNode;
2.职位升降级。例如技术组长2降级成为技术员工，被挂在技术组长1节点下:
declare @TechTeamLeadNode1 hierarchyid=0x5AC0;
declare @TechEmployeeNode3 hierarchyid=0x5ADE;
update EmployeeTreeTable set NodeId = @TechTeamLeadNode1.GetDescendant(@TechEmployeeNode3,null)
    where EmployeeId = 6
部分结果集为：
Path        NodeId    NodeLevel    EmployeeId    EmployeeName
/1/1/4/    0x5AE1    3                  6                     技术组长2
可见，技术组长2从/1/2变成了/1/1/4

总结：
SQL Server 2008提供的hierarchyid类型使我们能够灵活、方便的操作树型结构。关于hierarchyid还有很多深入的知识，很多灵活的用法，本文不可能一一涉及，这里仅是介绍一些基本用法，抛砖引玉，如果大家在以后的使用中发现什么问题或是更好的解决方案，请联系我。

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 一张表保存一个无限级树形目录

一张表保存一个无限级树形目录

看完仍有疑问？有类似问题直接问程序猿