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

最新推荐文章于 2024-07-17 17:53:42 发布

土豆赛叩

最新推荐文章于 2024-07-17 17:53:42 发布

阅读量299

点赞数

分类专栏： SQL/SQLServer

本文链接：https://blog.csdn.net/Vblegend_2013/article/details/81164114

版权

SQL/SQLServer 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

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

表结构+数据

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]           虚拟字段，节点的层深