公用表表达式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