利用临时表实现CTE递归查询

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

一、CTE递归查询简介

--CTE递归查询终止条件
在TSQL脚本中，也能实现递归查询，SQL Server提供CTE（Common Table Expression），只需要编写少量的代码，就能实现递归查询，本文详细介绍CTE递归调用的特性和使用示例，递归查询主要用于层次结构的查询，从叶级（Leaf Level）向顶层（Root Level）查询，或从顶层向叶级查询，或递归的路径（Path）。
一，递归查询原理
CTE的递归查询必须满足三个条件：初始条件，递归调用表达式，终止条件，CTE 递归查询的伪代码如下：

WITH cte_name ( column_name [,...n] )
AS
(
--Anchor member is defined
CTE_query_definition
UNION ALL
--Recursive member is defined referencing cte_name
CTE_query_definition
)
-- Statement using the CTE
SELECT * FROM cte_name
1，递归查询至少包含两个子查询：

第一个子查询称作定点（Anchor）子查询：定点查询只是一个返回有效表的查询，用于设置递归的初始值；
第二个子查询称作递归子查询：该子查询调用CTE名称，触发递归查询，实际上是递归子查询调用递归子查询；
两个子查询使用union all，求并集；
2，CTE的递归终止条件
递归查询没有显式的递归终止条件，只有当递归子查询返回空结果集（没有数据行返回）或是超出了递归次数的最大限制时，才停止递归。
默认的递归查询次数是100，可以使用查询提示（hint）：MAXRECURSION 控制递归的最大次数：OPTION( MAXRECURSION 16)；如果允许无限制的递归次数，使用查询提示：option(maxrecursion 0)；当递归查询达到指定或默认的 MAXRECURSION 数量限制时，SQL Server将结束查询并返回错误，如下：
The statement terminated. The maximum recursion 10 has been exhausted before statement completion.
事务执行失败，该事务包含的所有操作都被回滚。在产品环境中，慎用maxrecursion 查询提示，推荐通过 where 条件限制递归的次数。
3，递归步骤
step1：定点子查询设置CTE的初始值，即CTE的初始值Set0；
递归调用的子查询过程：递归子查询调用递归子查询；
step2：递归子查询第一次调用CTE名称，CTE名称是指CTE的初始值Set0，第一次执行递归子查询之后，CTE名称是指结果集Set1；
step3：递归子查询第二次调用CTE名称，CTE名称是指Set1，第二次执行递归子查询之后，CTE名称是指结果集Set2；
step4：在第N次执行递归子查询时，CTE名称是指Set(N-1)，递归子查询都引用前一个递归子查询的结果集；
Step5：如果递归子查询返回空数据行，或超出递归次数的最大限制，停止递归；

二、利用临时表实现CTE查询，主要是用来帮助理解CTE。

2.1创建测试数据

--创建表格实例
CREATE TABLE [dbo].[testtb](

[namea] [varchar](100) NULL,

[desca] [varchar](50) NULL

) ON [PRIMARY]

--插入数据
insert into testtb values('A1,A2,','AAAAA')

insert into testtb values('A3,','BBBBBB')

insert into testtb values('A5,A8,A9,','AAACCAA')

insert into testtb values('A6,A20,','AAANNNNAA')

2.2使用CTE查询

WITH testtb2 AS (
SELECT namea,desca,CHARINDEX(',',namea) STA,CHARINDEX(',',namea)-1 LENS FROM testtb
UNION ALL
SELECT namea,desca,CHARINDEX(',',namea,STA+1) STA,CHARINDEX(',',namea,STA+1)-STA-1 LENS FROM testtb2 WHERE STA<>0)
--SELECT * FROM testtb2

SELECT SUBSTRING(namea,STA-LENS,LENS) as [name],desca FROM testtb2
WHERE STA<>0
order by SUBSTRING(namea,STA-LENS,LENS)

2.3使用临时表查询

--分解查询步骤
SELECT * FROM dbo.testtb
SELECT namea,desca, CHARINDEX(',',namea) AS STA , CHARINDEX(',',namea)-1 AS Lens INTO #t1 FROM dbo.testtb
SELECT namea,desca, CHARINDEX(',',namea,STA+1) AS STA , CHARINDEX(',',namea,STA+1)-1-STA AS Lens INTO #t2 FROM #t1 WHERE STA>0
SELECT * FROM #t2
SELECT namea,desca, CHARINDEX(',',namea,STA+1) AS STA , CHARINDEX(',',namea,STA+1)-1-STA AS Lens INTO #t3 FROM #t2 WHERE STA>0
SELECT * FROM #t3
SELECT namea,desca, CHARINDEX(',',namea,STA+1) AS STA , CHARINDEX(',',namea,STA+1)-1-STA AS Lens INTO #t4 FROM #t3 WHERE STA>0
SELECT * FROM #t4
SELECT namea,desca, CHARINDEX(',',namea,STA+1) AS STA , CHARINDEX(',',namea,STA+1)-1-STA AS Lens FROM #t4 WHERE STA>0
--查询结果为空，则终止

--利用最后查询结果查询所需数据

SELECT SUBSTRING(b.namea,b.STA-LENs,b.Lens) AS namea, desca FROM (
SELECT * FROM#t1
UNION ALL SELECT * FROM #t2
UNION ALL SELECT * FROM#t3
UNION ALLSELECT * FROM #t4) b
WHERE b.STA>0.

--删除临时表

DROP TABLE #t1;DROP TABLE #t2;DROP TABLE #t3;DROP TABLE #t4

该过程未必是CTE的执行过程，但可以帮助我们理解CTE的查询。当出现错误时，可以按照分解步骤考虑出错的原因。例如无限递归错误，按照该分解步骤测试，看是否一直有查询结果，即无法终止。

碰见类似的查询时，我的思路与分步查询的方式一致，从而转换成CTE形式。