利用临时表实现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 ALL SELECT * FROM #t4) b
WHERE b.STA>0.
--删除临时表
DROP TABLE #t1;DROP TABLE #t2;DROP TABLE #t3;DROP TABLE #t4
该过程未必是CTE的执行过程,但可以帮助我们理解CTE的查询。当出现错误时,可以按照分解步骤考虑出错的原因。例如无限递归错误,按照该分解步骤测试,看是否一直有查询结果,即无法终止。
碰见类似的查询时,我的思路与分步查询的方式一致,从而转换成CTE形式。