公用表表达式（CTE）是SQL Server 2005中提供的一种新的解决方案。

公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。

CET 作用：

• （1）公用表表达式 (CTE) 能够引用其自身，从而创建递归 CTE。
• （2）在不需要常规使用视图时替换视图，也就是说，不必将定义存储在元数据中。
• （3）启用按从标量嵌套 select 语句派生的列进行分组，或者按不确定性函数或有外部访问的函数进行分组。
• （4）在同一语句中多次引用生成的表。
• (5)可以在用户定义的例程（如函数、存储过程、触发器或视图）中定义 CTE。

使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。

CTE 的结构

CTE 由表示 CTE 的表达式名称、可选列列表和定义 CET 的查询组成。定义 CTE 后，可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中对其进行引用，就像引用表或视图一样。CTE 也可用于 CREATE VIEW 语句，作为定义 SELECT 语句的一部分。

CTE 语法结构：

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

AS

( CTE_query_definition )

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

运行 CTE 的语句为：

SELECT

FROM expression_name

使用CTE时应注意点：

（1）CTE后面必须直接跟使用CTE的SQL语句，否则，CTE将失效。

WITH crs
AS(SELECT 编号 FROM EMPLOYER WHERE 员工 LIKE 'D%' )
--SELECT *FROM PERSON   -CTE后须直接跟使用CTE的语句，否则报错
SELECT *FROM DEPART WHERE 员工编号 IN (SELECT * FROM crs)

（2）CTE后可以接多个CTE，但只能使用一个with，多个CTE中间用逗号“，”分隔。

如 查找以‘D’开头、且年龄不小于平均年龄的员工的部门和工资

WITH
crs  AS(SELECT 编号 FROM EMPLOYER WHERE 员工 LIKE 'D%' ),
crs2 AS(SELECT AVG(年龄)AS平均年龄  FROM EMPLOYER ),
crs3 AS (SELECT 编号 FROM EMPLOYER WHERE 年龄>=(SELECT * FROM crs2) AND 编号 IN  (SELECT * FROM crs))

SELECT *FROM DEPART WHERE 员工编号 IN (SELECT * FROM crs3)

（3）不能在 CTE_query_definition 中使用以下子句：

COMPUTE 或 COMPUTE BY、ORDER BY（除非指定了 TOP 子句）、INTO 、带有查询提示的 OPTION 子句、FOR XML、FOR BROWSE

举例：

创建连个表并插入数据

CREATE TABLE EMPLOYER(编号 int,员工 char(6),年龄 int)
--插入数据
INSERT INTO EMPLOYER SELECT 1,'ZHANG',20
INSERT INTO EMPLOYER SELECT 2,'LI',21
INSERT INTO EMPLOYER SELECT 3,'WANG',22
INSERT INTO EMPLOYER SELECT 4,'ZHAO',23
INSERT INTO EMPLOYER SELECT 5,'DUAN',24
INSERT INTO EMPLOYER SELECT 6,'DUAN',25

CREATE TABLE DEPART(部门 char(10),员工编号 int,工资 int)
--插入数据
INSERT INTO DEPART SELECT 'A',1,100
INSERT INTO DEPART SELECT 'A',2,200
INSERT INTO DEPART SELECT 'A',3,300
INSERT INTO DEPART SELECT 'A',4,400
INSERT INTO DEPART SELECT 'B',5,500
INSERT INTO DEPART SELECT 'B',6,600

查询以‘D’开头员工的部门和工资

（1）嵌套
SELECT *
FROM DEPART
WHERE 员工编号 IN (SELECT 编号 FROM EMPLOYER WHERE 员工 LIKE 'D%' )
ORDER BY 部门,员工编号

结果：

部门   员工编号  工资

B             5     500
B             6     600

（2）公用表达式

WITH crs
AS(SELECT 编号 FROM EMPLOYER WHERE 员工 LIKE 'D%' )
SELECT *
FROM DEPART WHERE 员工编号 IN (SELECT * FROM crs)

结果：

部门   员工编号  工资

B             5     500
B             6     600

(3)表变量

DECLARE @TAB TABLE(编号 int)
INSERT INTO @TAB(编号)
(SELECT 编号 FROM EMPLOYER WHERE 员工 LIKE 'D%')
SELECT *
FROM DEPART WHERE 员工编号 IN (SELECT * FROM @TAB)

部门   员工编号  工资

B             5     500
B             6     600