公用表表达式CTE简单递归使用-简单树形结构

1.建表脚本

CREATE TABLE [dbo].[tb_tree](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL,
    [NodeName] [nvarchar](50) NULL,
 CONSTRAINT [PK_tb_tree] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

[Id]--主键自增

[ParentId]--树形结构中表示从属的父节点

[NodeName] --节点名称

2.插入测试数据

SET IDENTITY_INSERT [dbo].[tb_tree] ON
INSERT [dbo].[tb_tree] ([Id], [ParentId], [NodeName]) VALUES (1, 0, N'Root1')
INSERT [dbo].[tb_tree] ([Id], [ParentId], [NodeName]) VALUES (2, 1, N'Level1-1')
INSERT [dbo].[tb_tree] ([Id], [ParentId], [NodeName]) VALUES (3, 1, N'Level1-2')
INSERT [dbo].[tb_tree] ([Id], [ParentId], [NodeName]) VALUES (4, 2, N'Level1-1-1')
INSERT [dbo].[tb_tree] ([Id], [ParentId], [NodeName]) VALUES (5, 2, N'Level1-1-2')
INSERT [dbo].[tb_tree] ([Id], [ParentId], [NodeName]) VALUES (6, 2, N'Level1-1-3')
INSERT [dbo].[tb_tree] ([Id], [ParentId], [NodeName]) VALUES (7, 2, N'Level1-1-4')
INSERT [dbo].[tb_tree] ([Id], [ParentId], [NodeName]) VALUES (8, 3, N'Level1-2-1')
INSERT [dbo].[tb_tree] ([Id], [ParentId], [NodeName]) VALUES (9, 4, N'Level1-1-1-1')
INSERT [dbo].[tb_tree] ([Id], [ParentId], [NodeName]) VALUES (10, 4, N'Level1-1-1-2')
INSERT [dbo].[tb_tree] ([Id], [ParentId], [NodeName]) VALUES (11, 5, N'Level1-1-2-1')
SET IDENTITY_INSERT [dbo].[tb_tree] OFF

插入结构表示树形结构为：

Root1

 ┗---Level1-1

           ┗---Level1-1-1

                   ┗---Level1-1-1-1

                   ┗---Level1-1-1-2

           ┗---Level1-1-2

                   ┗---Level1-1-2-1

           ┗---Level1-1-3

 ┗---Level1-2

           ┗---Level1-2-1

 ┗---Level1-3

3.需求1：当需要从任意节点获取所有的子节点父节点列表时，如从Level1-2-1节点需要获取所有的父节点（Level1-2,Root1)

with treeinfo as
(
   select Id,ParentId,NodeName from tb_tree where id = 8
   union all
   select t.Id,t.parentId,t.NodeName from treeinfo ti
   inner join tb_tree t on ti.ParentId = t.ID
)

select * from treeinfo 

查询结果如下：

Id 　　　　  ParentId    NodeName
----------- ----------- --------------------------------------------------
10 　         4 　　　　   Level1-1-1-2
4 　　        2               Level1-1-1
2 　　        1               Level1-1
1 　　        0               Root1

需求2：从父节点获取所有层级的子节点，如从节点Level1-1查找所有字节点

with treeinfo as(
    select Id,ParentId,NodeName from tb_tree where id=2
    union all
    select t.Id,t.ParentId,t.NodeName from treeinfo ti 
    inner join tb_tree t on ti.id = t.Parentid
)

select * from treeinfo

基本同需求1写法差不多，查询结果如下：

Id              ParentId     NodeName
----------- ----------- --------------------------------------------------
2               1               Level1-1
4               2               Level1-1-1
5               2               Level1-1-2
6               2               Level1-1-3
7               2               Level1-1-4
11             5               Level1-1-2-1
9               4               Level1-1-1-1
10             4               Level1-1-1-2