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