SQLSERVER 查询树状结构的子节点，或实现模板继承

最新推荐文章于 2023-10-02 16:06:07 发布

土豆赛叩

最新推荐文章于 2023-10-02 16:06:07 发布

阅读量327

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL/SQLServer

本文链接：https://blog.youkuaiyun.com/Vblegend_2013/article/details/81164114

SQL/SQLServer 专栏收录该内容

7 篇文章

订阅专栏

本文介绍如何使用SQL查询语句查询树状结构数据表中的子节点和父节点，包括创建表结构、插入数据以及实现模板继承的概念。通过示例展示了如何根据模板ID查找其子模板及父模板的层级关系。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询树状结构的子节点，或实现模板继承

表结构+数据

CREATE TABLE [dbo].[TBL_Table](
	[TemplateId] [int] IDENTITY(2000000,1) NOT NULL,
	[BaseId] [int] NULL,
	[TemplateName] [varchar](50) NOT NULL,
	[EquipmentTemplateId] [int] NULL,
 CONSTRAINT [PK_TBL_Table] PRIMARY KEY CLUSTERED 
(
	[TemplateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[TBL_Table] ON 
INSERT [dbo].[TBL_Table] ([TemplateId], [BaseId], [TemplateName], [EquipmentTemplateId]) VALUES (2000000, NULL, N'基础模板', NULL)
INSERT [dbo].[TBL_Table] ([TemplateId], [BaseId], [TemplateName], [EquipmentTemplateId]) VALUES (2000001, 2000000, N'测试模板1', NULL)
INSERT [dbo].[TBL_Table] ([TemplateId], [BaseId], [TemplateName], [EquipmentTemplateId]) VALUES (2000002, 2000000, N'测试模板2', NULL)
INSERT [dbo].[TBL_Table] ([TemplateId], [BaseId], [TemplateName], [EquipmentTemplateId]) VALUES (2000003, 2000001, N'测试模板3', NULL)
INSERT [dbo].[TBL_Table] ([TemplateId], [BaseId], [TemplateName], [EquipmentTemplateId]) VALUES (2000004, 2000002, N'测试模板4', NULL)
INSERT [dbo].[TBL_Table] ([TemplateId], [BaseId], [TemplateName], [EquipmentTemplateId]) VALUES (2000005, 2000003, N'测试模板5', NULL)
INSERT [dbo].[TBL_Table] ([TemplateId], [BaseId], [TemplateName], [EquipmentTemplateId]) VALUES (2000006, 2000004, N'测试模板6', NULL)
SET IDENTITY_INSERT [dbo].[TBL_Table] OFF

查询子节点


WITH TreeChilds
AS
(SELECT  [TemplateId],[TemplateName],[BaseId],0 AS Level FROM [TBL_Table] WHERE [TemplateId] = 2000002
UNION ALL 
SELECT D.[TemplateId],D.[TemplateName],D.[BaseId],Child.[Level] + 1
FROM [TBL_Table] AS D
INNER JOIN TreeChilds AS Child ON D.[BaseId] = Child.[TemplateId]
)SELECT [TemplateId],[TemplateName],[Level] AS ChildLevel FROM TreeChilds ORDER BY [TemplateId]

查询父节点

WITH TreeParents
AS
(SELECT  [TemplateId],[TemplateName],[BaseId],0 AS Level FROM [TBL_Table] WHERE [TemplateId] = 2000006
UNION ALL 
SELECT D.[TemplateId],D.[TemplateName],D.[BaseId],Child.[Level] + 1
FROM [TBL_Table] AS D
INNER JOIN TreeParents AS Child ON Child.[BaseId] =  D.[TemplateId]
)SELECT [TemplateId],[TemplateName],[Level] AS ChildLevel FROM TreeParents ORDER BY [TemplateId]

字段解释

[TBL_Table]       数据表名
[TemplateId]   表主键ID
[TemplateName] 表字段
[BaseId]       表字段，父节点的[TemplateId]
[Level]           虚拟字段，节点的层深