sqlserver取第一条数据_数据库大师成长日记：如何在SQL中正确使用公用表表达式...

对数据库有过了解的朋友，对本地表(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 视图名1aswith cte名称(字段列表，可省略)as ( select 字段列表 from 表名)select 字段列表 from 表名(或cte);

在视图中使用CTE能够大大提高视图的灵活性，也使得我们常用的视图有了些编程元素，不再是干巴巴的一条查询语句了。

如果您对公用表表达式有进一步兴趣，您可以移步到我写的《实战如何在SQL中使用公用表表达式》做更深入的了解。

希望对您有所帮助！