with as 的递归使用方法

曾经终败给现在 2022-05-27 23:56 161阅读 0赞

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

### **一、递归公用表表达式** ###

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

![SouthEast][]

**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将失效。

![SouthEast 1][]  
  
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 失效，报错

![SouthEast 2][]

**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)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70][]

[SouthEast]: /images/20220528/b376d31165994bcb926ceb8a15b226c0.png
[SouthEast 1]: /images/20220528/7627acbb7afd4f07a6f07713b7622e6b.png
[SouthEast 2]: /images/20220528/463307be39694215b29e753ddb615401.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70]: /images/20220528/1d2ea19ab2c44276b2d731c050a1cfe4.png