CTE - Common table Expressions（SQL）

MAIN:

类似于子查询，但是可以完成子查询所无法重用，也无法支持多个需求的问题。

当我们有相当复杂的需求的时候，一种常见的方法用子查询的内容建立临时表，优点是连接和过滤表达式可以支持多个需求，缺点是性能会很差，并且耗费很多的系统资源。

CTE是一种比较优秀的方法吧。因为他运用在内存中，所以不存在物理空间的操作。并且CTE就像是命名一个对象，具备了重用性质，这点很友好，并且灵活性会很棒。

 

下面是语法：

WITH <expression_name> [(column_name [,...n])]
    AS
    ( CTE_query_definition)
    [, <another_expression>]
<query>

解释一波：

定义形式是以with开头，后跟一列输出列，然后是as关键字，后面是一个select语句。

 

可能会有点抽象，直接上一个实例code就可以理解了。

建一个测试表：

-- create过程
drop table product
create table product(
productId varchar(20),
productName varchar(20),
productColor varchar(10),
productPrice money
)
go

-- 传值
insert into product values('1001','aa1','red','3.21')
insert into product values('1002','aa2','blue','4.21')
insert into product values('1003','aa3','black','5.21')
insert into product values('1004','aa4','blue','6.21')
insert into product values('2001','bb1','blue','5.51')
insert into product values('2002','bb2','red','6.51')
insert into product values('2003','bb3','black','7.51')
insert into product values('2004','bb4','red','8.51')

结果是这个样子的：

 

测试语句：

with blueProduct(productId,productName) as
(
	select productId,productName
	from product
	where productColor = 'blue'
)

select productId,productName 
from blueProduct

 

result：

 

需要注意的是，单一的CTE是无法执行的，必须后接查询才能使用。

在定义完了CTE之后，就像是一个表一样，后续的所有的脚本都可以进行引用。