对数据库有过了解的朋友,对本地表(table)肯定不会陌生,本地表(table)是关系型数据库最核心的存储单元。无论是商业数据库像微软的SQL Server、甲骨文的Oracle、IBM的DB2,还是开源的SQLite、MySQL、PostgreSQL、FirebirdSQL等等,都是通过行列二维表格存储数据。甚至某些NoSQL数据库,比如在MongoDB中都能发现表的影子。
大部分数据库系统除了提供基本的本地表之外,还会提供一些与本地表结构类似但作用不同的特殊表。比如在SQLServer中还提供了临时表、表变量、公用表、表类型、内存表等,她们的含义和用途各不相同。
今天我们就详细说说SQLServer中的公用表表达式。想要了解临时表(查看)、表变量(查看)及二者对比(查看)的朋友,我前面有三个独立章节描述,请通过对应的链接查看。
什么是公用表表达式?
官方对公用表表达式的定义为:
公用表表达式(CTE)是指定的临时命名的结果集。
定义很简单,公用表表达式首先是一个表达式,其作用是作为结果集,而且是临时命名用后即焚的。
公用表表达式(CTE)源自简单查询,并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。也可用在CREATE VIEW语句中,作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用,称为递归公用表表达式。
使用公用表表达式有什么好处?
使用公用表表达式有下列优势:
- 可以定义递归公用表表达式(CTE)
- CTE简洁的实现了视图和子查询,却用后即焚
- GROUP BY语句可以直接作用于子查询所得的标量列
- 可以在一个语句中多次引用公用表表达式(CTE)
如何定义公用表表达式?
公用表表达式的语法定义非常简单:
with cte名称 (字段列表,可省略) as ( select 字段列表 from 表名 )
这里要注意,定义公用表表达式时,不能使用分号“;”结束,因为公用表表达式依附于紧接着调用它的那条语句,而且只能被紧接着的语句调用,调用的语句可以是select、insert、update或delete语句,而且可以在语句中被多次调用。
因为公用表表达式是依附与紧接着调用的语句的,所以在SQL脚本,比如存储过程或自定义函数中,cte名称是可以重复使用的。
如何一次性定义多个公用表表达式?
公用表表达式可以一次性定义多个,后定义的可以引用先定义的,定义的方式使用逗号“,”隔开,语法格式如下:
with cte名称1 (字段列表,可省略) as ( select 字段列表 from 表名 ) ,cte名称2(字段列表,可省略) as ( select查询,可调用cte名称1 ) ...
这里要注意,后面的表达式可以引用前面的表达式,但前面的表达式不可以引用后面的表达式。当然后面的表达式也可以不引用任何表达式。所有批量定义的表达式,均可在紧接着的调用语句中直接调用。
如何定义递归调用的公用表表达式?
公用表表达式可以定义递归调用,使用Union All连接,第一条是基础语句、后面的可以直接引用结果集,语法格式如下:
with cte名称 (字段列表,可省略) as ( --基础语句 select 字段列表 from 表名 union all --递归语句1 select 字段列表 from cte名称 Join cte名称 uinon all --递归语句2 ... )
公用表表达式内部递归调用很容易造成死循环,为了避免死循环,可以通过在紧接着的语句中加入option(MAXRECURSION 递归级别)子句限制,最好的办法是增加一个标识层级的字段,调用时指定调用的层级。
如何在视图中使用公用表表达式?
了解了公用表达式如何定义,在视图(view)中定义就非常简单,语法如下:
create view 视图名1 as with cte名称(字段列表,可省略) as ( select 字段列表 from 表名 ) select 字段列表 from 表名(或cte);
在视图中使用CTE能够大大提高视图的灵活性,也使得我们常用的视图有了些编程元素,不再是干巴巴的一条查询语句了。
如果您对公用表表达式有进一步兴趣,您可以移步到我写的《实战如何在SQL中使用公用表表达式》做更深入的了解。
希望对您有所帮助!