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

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

表结构+数据

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