公用表表达式（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)【SQL|with as 的递归使用方法】
文章图片
推荐阅读
- 数据库|SQL行转列方式优化查询性能实践
- SqlServer|sql server的UPDLOCK、HOLDLOCK试验
- SQL|SQL基本功（五）--函数、谓词、CASE表达式
- SQL|SQL基本功（三）-- 聚合与排序
- web挖洞|HACK学习黑帽子Python--漏洞检测脚本快速编写
- sqlite|python中用SQLite3添加 主键约束 唯一约束 非空约束 外键约束（约束的介绍以及设置）
- python|python中使用SQLite3对数据库的基本操作（基于ubuntu操作系统）
- TDSQL | DTS for PostgreSQL 逻辑复制详解
- TDSQL | 《checkpoint 原理浅析》
- TDSQL-A 技术架构演进及创新实践