结构如何利用SQLServer 构建树状结构

1. SQLServer构建树状结构

在数据库中,有时候需要将数据以树状结构进行存储,比如商品分类、部门管理等等,这些数据的关系都是通过父子节点进行连接的。本文将介绍如何利用SQLServer构建树状结构。

1.1 基本概念

在树状结构中,每个节点都有一个父节点,并且可能有多个子节点。一般来说,树状结构有如下特点:

每个节点都有0个或多个子节点

每个非根节点都有且仅有一个父节点

树中任意两个点之间都有唯一的路径

在SQLServer中,我们使用递归查询来构建树状结构,这需要使用到一个叫做CTE(Common Table Expression)的语法。

1.2 CTE语法

CTE是一种临时的命名结果集,只存在于查询的执行过程中。CTE语法使用WITH关键字进行定义,语法格式如下:

WITH 名称(列名1, 列名2,...,列名n) AS (

SELECT 列名1, 列名2,...,列名n FROM 表名 WHERE 条件

)

SELECT * FROM 名称 WHERE 条件;

这里值得注意的是,WITH后最好跟上AS关键字(有时也可以不写),然后是一个括号内的子查询。这个子查询也可以连接其他表或视图,最后我们需要使用SELECT语句对之前定义的名称进行查询。

2. 实现过程

下面我们将介绍如何利用CTE语法建立一个树状结构。我们以以下表结构为例:

CREATE TABLE [dbo].[TreeTable]

(

[Id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,

[Name] nvarchar(50) NOT NULL,

[ParentId] int DEFAULT 0 NOT NULL,

)

在这里,每条记录都包含一个唯一标识Id和一个名称Name,还有一个ParentId表示该记录的父记录标识。

2.1 创建测试数据

首先我们需要向表中插入一些测试数据。这里我们先插入一个根节点,再插入一些子节点,代码如下:

INSERT INTO [dbo].[TreeTable] ([Name], [ParentId]) VALUES ('根节点', 0)

INSERT INTO [dbo].[TreeTable] ([Name], [ParentId]) VALUES ('子节点1', 1)

INSERT INTO [dbo].[TreeTable] ([Name], [ParentId]) VALUES ('子节点2', 1)

INSERT INTO [dbo].[TreeTable] ([Name], [ParentId]) VALUES ('子节点3', 2)

INSERT INTO [dbo].[TreeTable] ([Name], [ParentId]) VALUES ('子节点4', 2)

INSERT INTO [dbo].[TreeTable] ([Name], [ParentId]) VALUES ('子节点5', 4)

INSERT INTO [dbo].[TreeTable] ([Name], [ParentId]) VALUES ('子节点6', 5)

插入数据后,我们可以使用SELECT * FROM [dbo].[TreeTable]进行简单的查询,看一下表中数据的情况:

SELECT * FROM [dbo].[TreeTable]

其结果如下:

Id

Name

ParentId

1

根节点

0

2

子节点1

1

3

子节点2

1

4

子节点3

2

5

子节点4

2

6

子节点5

4

7

子节点6

5

2.2 递归查询

我们需要使用递归查询来构建树状结构。在这里,我们定义一个名称Tree,然后使用CTE语法来实现。具体代码如下:

WITH Tree AS (

SELECT [Id], [Name], [ParentId] FROM [dbo].[TreeTable] WHERE [ParentId] = 0

UNION ALL

SELECT t2.[Id], t2.[Name], t2.[ParentId] FROM [dbo].[TreeTable] t2, Tree t1

WHERE t2.[ParentId] = t1.[Id]

)

SELECT * FROM Tree;

这里我们定义了一个名称Tree,注意到第一条SELECT语句,它的作用是找出表中ParentId为0的根节点。然后,在这个SELECT语句的基础上,使用UNION ALL将第二条SELECT语句结果与之合并。这条SELECT语句的作用是根据t1节点,将其子节点t2与之匹配,构造树状结构。最后我们使用SELECT语句对Tree进行查询即可。

运行查询后,可以得到以下结果:

Id

Name

ParentId

1

根节点

0

2

子节点1

1

3

子节点2

1

4

子节点3

2

5

子节点4

2

6

子节点5

4

7

子节点6

5

这个结果就是我们构建的树的全部内容。我们来分析一下这里的查询语句。

2.3 查询语句分析

在上面的递归查询语句中,我们首先从表中找出ParentId为0的根节点,这些节点就是我们需要的结果集。这部分语句:

SELECT [Id], [Name], [ParentId] FROM [dbo].[TreeTable] WHERE [ParentId] = 0

然后我们采用UNION ALL将这些结果集合并起来。递归查询部分:

SELECT t2.[Id], t2.[Name], t2.[ParentId] FROM [dbo].[TreeTable] t2, Tree t1 WHERE t2.[ParentId] = t1.[Id]

这部分的意思是,找到所有t1的子节点的子节点,将它们与t1合并成结果集,并满足t2的ParentId为t1的Id。这里使用t1.[Id],是因为t1是临时表Tree中的数据。

3. 实现扩展

实际应用中,我们可能需要对树状结构进行一些操作,比如增加、删除、修改节点等等。这里介绍一些扩展内容。

3.1 增加节点

如果需要增加节点,我们需要插入一条新记录,设置ParentId为所需节点的父节点Id即可。示例代码如下:

INSERT INTO [dbo].[TreeTable] ([Name], [ParentId]) VALUES ('子节点7', 6)

3.2 删除节点

如果需要删除节点,我们需要将该节点及其所有子节点从表中删除。示例代码如下:

WITH TreeDelete AS (

SELECT [Id] FROM [dbo].[TreeTable] WHERE [Id] = 7

UNION ALL

SELECT t2.[Id] FROM [dbo].[TreeTable] t2, TreeDelete t1

WHERE t2.[ParentId] = t1.[Id]

)

DELETE FROM [dbo].[TreeTable] WHERE [Id] IN (SELECT [Id] FROM TreeDelete)

这里首先查询出需要删除的节点(删除节点的Id为7),然后构造一个临时表TreeDelete,可以看到这个部分跟递归查询非常类似。然后我们将临时表中的节点Id与原表中的节点Id进行删除操作。

3.3 修改节点

如果需要修改节点,我们需要先查询出该节点,然后进行修改。示例代码如下:

UPDATE [dbo].[TreeTable] SET [Name] = '修改后的子节点' WHERE [Id] = 2

这里我们将Id为2的节点名称改为“修改后的子节点”。注意到我们并没有涉及到父节点,这是因为在树状结构里,节点名称是独立的,不需要考虑与其他节点之间的关系。

4. 总结

本文介绍了如何利用SQLServer构建树状结构,目前应用非常广泛。主要是通过CTE语法构造递归查询,使用查询结果构建树状结构。同时,本文也介绍了如何在树状结构中增加、删除、修改节点等基本操作,为进一步开发打下了基础。当然,如果需要特别复杂的树状结构,还需要结合其他技术一起使用,例如前端框架、缓存技术等等。

免责声明:本文来自互联网,本站所有信息(包括但不限于文字、视频、音频、数据及图表),不保证该信息的准确性、真实性、完整性、有效性、及时性、原创性等,版权归属于原作者,如无意侵犯媒体或个人知识产权,请来电或致函告之,本站将在第一时间处理。猿码集站发布此文目的在于促进信息交流,此文观点与本站立场无关,不承担任何责任。

数据库标签