withas的递归使用方法

公用表表达式(Common Table Expression)是SQL SERVER 中的一个特性.CTE可以看作是一个临时的结果集,可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中被多次引用。使用公用表达式可以让语句更加清晰简练.
按照是否递归,可以将公用表(CTE)表达式分为递归公用表表达式和非递归公用表表达式.
 

一、递归公用表表达式

--以城市,省份,地区为数据 SELECT [ID] ,[Value] ,[Type] ,[ParentID] FROM [dbo].[ProvinceCity_Test] order BY Type

1.1举例:以西安基础查询所有父级结构

WITH CTE_TEMP --公用表表达式(Common Table Expression) as( select * FROM ProvinceCity_Test where value LIKE '%西安%' union ALL SELECT a.* FROM ProvinceCity_Test a INNER JOIN CTE_TEMP b ON a.ID = b.ParentID --父子级关系,递归,递归部分不允许使用外部联接(不允许使用left join等) ) SELECT * FROM CTE_TEMP -- CTE后面必须直接跟使用CTE的SQL语句(如select、insert、update等),否则,CTE将失效。

1.2 CTE失效问题

WITH CTE_TEMP --公用表表达式(Common Table Expression) as( select * FROM ProvinceCity_Test where value LIKE '%西安%' union ALL SELECT a.* FROM ProvinceCity_Test a INNER JOIN CTE_TEMP b ON a.ID = b.ParentID --父子级关系,递归,递归部分不允许使用外部联接(不允许使用left join等) ) -- CTE后面必须直接跟使用CTE的SQL语句(如select、insert、update等),否则,CTE将失效。 SELECT * from ProvinceCity_Test SELECT * FROM CTE_TEMP -- CTE_TEMP 失效,报错

1.3递归次数
(1)OPTION 子句中的一个 0 到 32,767 之间的值来限制特定语句所允许的递归级数,以防止出现无限循环。
(2)SQL SERVER服务器范围内的默认值是 100。如果指定 0,则没有限制。

WITH CTE_TEMP --公用表表达式(Common Table Expression) as( select * FROM ProvinceCity_Test where value LIKE '%西安%' union ALL SELECT a.* FROM ProvinceCity_Test a inner JOIN CTE_TEMP b ON a.ID = b.ParentID --父子级关系,递归,递归部分不允许使用外部联接(不允许使用left join等) ) SELECT * FROM CTE_TEMP OPTION(MAXRECURSION 2) 

原创文章,作者:优速盾-小U,如若转载,请注明出处:https://www.cdnb.net/bbs/archives/30975

(0)
上一篇 2024年6月22日 12:36
下一篇 2024年6月22日

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

优速盾注册领取大礼包www.cdnb.net
/sitemap.xml