SQL递归

我们先导入测试数据：

CREATE TABLE [dbo].[TestWithTable](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[parentid] [int] NOT NULL,
	[name] [nchar](10) NULL,
 CONSTRAINT [PK_TestWithTable] 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]

GO
SET IDENTITY_INSERT [dbo].[TestWithTable] ON 

GO
INSERT [dbo].[TestWithTable] ([id], [parentid], [name]) VALUES (1, 0, N'一级（1）     ')
GO
INSERT [dbo].[TestWithTable] ([id], [parentid], [name]) VALUES (2, 0, N'一级（2）     ')
GO
INSERT [dbo].[TestWithTable] ([id], [parentid], [name]) VALUES (3, 0, N'一级（3）     ')
GO
INSERT [dbo].[TestWithTable] ([id], [parentid], [name]) VALUES (4, 1, N'二级（1-1）   ')
GO
INSERT [dbo].[TestWithTable] ([id], [parentid], [name]) VALUES (5, 1, N'二级（1-2）   ')
GO
INSERT [dbo].[TestWithTable] ([id], [parentid], [name]) VALUES (6, 2, N'二级（2-1）   ')
GO
INSERT [dbo].[TestWithTable] ([id], [parentid], [name]) VALUES (7, 2, N'二级（2-2）   ')
GO
INSERT [dbo].[TestWithTable] ([id], [parentid], [name]) VALUES (8, 3, N'二级（3-1）   ')
GO
INSERT [dbo].[TestWithTable] ([id], [parentid], [name]) VALUES (9, 4, N'三级（1-1-1） ')
GO
INSERT [dbo].[TestWithTable] ([id], [parentid], [name]) VALUES (10, 5, N'三级（1-2-1） ')
GO
INSERT [dbo].[TestWithTable] ([id], [parentid], [name]) VALUES (11, 7, N'三级（2-2-1） ')
GO
SET IDENTITY_INSERT [dbo].[TestWithTable] OFF
GO
ALTER TABLE [dbo].[TestWithTable] ADD  CONSTRAINT [DF_TestWithTable_parentid]  DEFAULT ((0)) FOR [parentid]
GO

接下来我们了解什么是WITH AS

WITH AS短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高，也可以在UNION ALL的不同部分，作为提供数据的部分。

对于UNION ALL，使用WITH AS定义了一个UNION ALL语句，当该片断被调用2次以上，优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。

使用方法

我们先写一个嵌套查询，如下：

select * from TestWithTable where parentid in (1,7)

我们再使用with as

with this_cts
as
(
select 1 as col union all select 7 as col
)
select * from TestWithTable where parentid in (select * from this_cts)

注意：

1.with as 后面必须要接上使用这个的查询语句，否则这个cte不生效

2.如果使用多个with as ，那么使用逗号隔开

下面我们来使用递归

我们建表的目的就是建一个树形结构，通过parentid找到它的父级，这里我们为了标注树形结构使用'>|'符号分隔，每一个这个代表的一级，下面我们看代码：

WITH TEST_CTE 
AS
(
SELECT TBIE.id,TBIE.parentid,TBIE.name,cast( '>|' as nvarchar(1000)) as treeCode
FROM TestWithTable TBIE
WHERE TBIE.parentid = 0
UNION ALL
SELECT CTBIE.id,CTBIE.parentid,CTBIE.name,cast( CTE.treeCode+'>|' as nvarchar(1000)) as treeCode
FROM TestWithTable CTBIE

INNER JOIN TEST_CTE CTE ON CTBIE.parentid=CTE.id 
)
SELECT * FROM TEST_CTE  

结果如下：

关于"在递归查询 "TEST_CTE" 的列 "treeCode" 中，定位点类型和递归部分的类型不匹配。"

这个原因是因为在union 联合查询的时候，两边需要连接的表字段要求统一，所以把这个变化的列强转成同一种类型即可