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语法构造递归查询,使用查询结果构建树状结构。同时,本文也介绍了如何在树状结构中增加、删除、修改节点等基本操作,为进一步开发打下了基础。当然,如果需要特别复杂的树状结构,还需要结合其他技术一起使用,例如前端框架、缓存技术等等。