2005每日一学] 使用公用表表达式（8）

[SQLServer_2005]

使用公用表表达式

公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。
CET 可用于：
• 创建递归查询。。
• 在不需要常规使用视图时替换视图，也就是说，不必将定义存储在元数据中。
• 启用按从标量嵌套 select 语句派生的列进行分组，或者按不确定性函数或有外部访问的函数进行分组。
• 在同一语句中多次引用生成的表。
使用 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 <column_list>
FROM expression_name

在 SQL Server 2005 中，当某个查询引用递归 CTE 时，它即被称为“递归查询”。递归查询通常用于返回分层数据，例如：显示某个组织图中的雇员或物料清单方案（其中父级产品有一个或多个组件，而那些组件可能还有子组件，或者是其他父级产品的组件）中的数据。
递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。在 SQL Server 的早期版本中，递归查询通常需要使用临时表、游标和逻辑来控制递归步骤流。
如果递归 CTE 组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。

实例：
/**创建测试表**/
CREATE TABLE T1
(
ID INT IDENTITY(1,1),
TNAME VARCHAR(10),
PID INT
)
GO
/**插入测试数据**/
INSERT INTO T1 SELECT 'A',0
UNION ALL SELECT 'B',0
UNION ALL SELECT 'C',0
UNION ALL SELECT 'a',1
UNION ALL SELECT 'aa',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'bb',2
UNION ALL SELECT 'bbb',2
UNION ALL SELECT 'c',3
GO
/**递归查询**/
WITH TEMP(ID,TNAME,PID,LEVEL)
AS
(
SELECT ID,TNAME,PID,0 AS LEVEL
FROM T1
WHERE PID=0-- AND TNAME='A'
UNION ALL
SELECT T1.ID,T1.TNAME,T1.PID,LEVEL+1
FROM T1
INNER JOIN TEMP
ON T1.PID=TEMP.ID
)
SELECT ID,TNAME,PID,LEVEL
FROM TEMP
ORDER BY PID,LEVEL
GO
/**查询结果
1 A 0 0
2 B 0 0
3 C 0 0
4 a 1 1
5 aa 1 1
6 b 2 1
7 bb 2 1
8 bbb 2 1
9 c 3 1
**/
/**然后我们再插入一条测试数据：**/
INSERT INTO T1 SELECT 'a1',4
GO
/**再执行查询：
1 A 0 0
2 B 0 0
3 C 0 0
4 a 1 1
5 aa 1 1
6 b 2 1
7 bb 2 1
8 bbb 2 1
9 c 3 1
10 a1 4 2
**/
/**最后修改查询如下：**/
WITH TEMP(ID,TNAME,PID,LEVEL)
AS
(
SELECT ID,TNAME,PID,0 AS LEVEL
FROM T1
WHERE PID=0 AND TNAME='A'
UNION ALL
SELECT T1.ID,T1.TNAME,T1.PID,LEVEL+1
FROM T1
INNER JOIN TEMP
ON T1.PID=TEMP.ID
)
SELECT ID,TNAME,PID,LEVEL
FROM TEMP
ORDER BY PID,LEVEL
GO
/**结果为：
1 A 0 0
4 a 1 1
5 aa 1 1
10 a1 4 2
**/
/**删除测试**/
TRUNCATE TABLE T1
DROP TABLE T1


更多详细资料参考“Books Online"

以往帖子：
[2005每日一学]DDL触发器（1）
http://community.csdn.net/Expert/topic/4910/4910909.xml?temp=.5234644
2005每日一学]XML数据类型（2）
http://community.csdn.net/Expert/topic/4913/4913373.xml?temp=.579159
2005每日一学]排名函数（3）
http://community.csdn.net/Expert/topic/4918/4918884.xml?temp=.3263056
2005每日一学]APPLY运算符（4）
http://community.csdn.net/Expert/topic/4921/4921633.xml?temp=.4351465
2005每日一学]使用大值数据类型（5）
http://community.csdn.net/Expert/topic/4923/4923551.xml?temp=.471081
2005每日一学]数据库引擎错误处理（6）
http://community.csdn.net/Expert/topic/4926/4926663.xml?temp=.5996057
2005每日一学] 元数据视图（7）
http://community.csdn.net/Expert/topic/4934/4934100.xml?temp=.3174555