CTE的介绍与使用

 

1、什么是CTE

CTE可以被认为是在单个SELECT、INSERT 、UPDATE、DELETE或CREATE VIEW语句的执行范围内定义的临时结果集。CTE类似于派生表，因为它不作为对象存储，并且仅在查询期间持续。与派生表不同，CTE可以自引用，并且可以在同一查询中多次使用。

 

2、CTE作用：

•     创建递归查询

• 在一般不是必要使用视图时替换视图，这样的话，就不需要存储元数据

• 在同一语句中，多次涉及到一个结果表

 

使用CTE具有提高可读性和易于维护复杂查询的优点。查询可以分为单独的简单逻辑构建块。然后，可以使用这些简单的块来构建更复杂的临时CTE，直到生成最终结果集。

 

CTE可以在函数、存储过程、触发器、视图中定义使用。

 

3、CTE的结构

 

CTE 由CTE的表达式名称，可选列列表和定义CTE的查询组成。定义CTE后，可以在SELECT,INSERT,UPDATE,DELETE语句中像表或视图一样引用它。CTE可以在 CREATE VIEW语句中用作其定义SELECT语句的一部分。

 

CTE的基本语法结构是：

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

--只有在查询定义中提供了所有结果列的不同名称时，列名列表才是可选的。

--在语句中使用CTE：

SELECT <column_list>

FROM expression_name;

4、举个例子

 

    下面的例子展示了CTE的组成：表达式名称，可选列列表，查询语句。

-- 定义CTE表达式名称和列 .

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)

AS

--定义查询语句.

(

    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear

    FROM Sales.SalesOrderHeader

    WHERE SalesPersonID IS NOT NULL

)

-- 对外使用CTE进行查询.

SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear

FROM Sales_CTE

GROUP BY SalesYear, SalesPersonID

ORDER BY SalesPersonID, SalesYear;

部分结果展示：

SalesPersonID TotalSales  SalesYear

------------- ----------- -----------

274           4           2001

274           20          2002

274           14          2003

274           10          2004

275           56          2001

275           139         2002

275           169         2003