SQL基础：SQL Server使用T-SQL进阶之公用表表达式(CTE)

在编写T-SQL代码时，往往需要临时存储某些结果集。前面我们已经广泛使用和介绍了两种临时存储结果集的方法：临时表和表变量。除此之外，还可以使用公用表表达式的方法。

公用表表达式(Common Table Expression)是SQL Server2005版本的引入的一个特性。CTE可以看组是一个临时的结果集，可以再接下来来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中多次引用。

一、3种方法比较

使用公用表达式CTE可以让语句更加清晰简练。与公用表达式作用类似的还有临时表和表变量。下面给出三种方法的对比。

• 临时表#：需要在临时数据库TempDB中通过I/O操作来创建表结构，一旦用户退出SQL Server环境则自动被删除。
• 表变量@：在内存中以表结构的形式存在，其定义与变量一致，其使用与表类似，不需要产生I/O。
• 公用表表达式with as：定义在内存中保存的临时存储结果集对象，不产生I/O，不需要按照表变量这样定义，使用方法和表类似。可以自己引用，也可以再查询中被多次引用。

1、使用CTE好处

根据微软对CTE好处的描述，可以归结为四点:

• 可以定义递归公用表表达式(CTE)
• 当不需要将结果集作为视图被多个地方引用时，CTE可以使其更加简洁
• GROUP BY 语句可以直接作用于子查询所得的标量列
• 可以在一个语句中多次引用公用表表达式(CTE)

二、WITH AS的含义

WITH AS-做子查询部分(subquery factoring)。

它用于定义一个SQL片段，该片段会被是整个SQL语句所用到。如果WITH AS所以定的表名被调用两次以上，则优化器会自动将WITH AS所获取的数据放入临时表里，如果只是被调用一次，则不会。

可以通过materialize将WITH AS短语里的数据强制放入全局临时表里。

WITH AS可以被紧跟着的一条SQL语句所使用多次，但不能被紧跟着的多条SQL语句使用。

WITH B AS
(
SELECT * FROM xxx WHERE Id > 5
)
SELECT * FROM B