论常见表达式的应用

王剑波

(湖南人文科技学院 计算机科学技术系，湖南 娄底 417000)

由于关系数据库查询效率不高的缺点，我们需要对关系数据进行优化。查询数据库是我们经常要做的一种工作，查询效率的高低直接影响我们的工作水平。选择了一个高性能的数据库产品不等于就有一个好的数据库应用系统，如果数据库系统设计不合理，不仅会增加客户端和服务器端程序的编程和维护的难度，而且还会影响系统实际运行的性能。递归查询是我们在查询数据中最经常用到的一种方式。如果不对递归级别数加以限制，且存在循环，则会产生无限递归，每次迭代期间选择的行数可能会以指数方式增加，严重影响数据库性能，而导致错误。

递归是指一个过程或函数在其定义或说明中又直接或间接调用自身的一种方法[1-2]，它通常把一个大型复杂的问题层层转化为一个与原问题相似的规模较小的问题来求解，递归策略只需少量的程序就可描述出解题过程所需要的多次重复计算，大大地减少了程序的代码量。递归的能力在于用有限的语句来定义对象的无限集合。用递归思想写出的程序往往十分简洁易懂， 一般来说，递归需要有边界条件、递归前进段和递归返回段。当边界条件不满足时，递归前进；当边界条件满足时，递归返回。

常见表达式 (Common Table Expression,CTE)是一个可以由定义语句引用的临时命名的结果集[3-4]。在它们的简单形式中，可以将 CTE 视为更类似于非持续性类型视图的派生表的改进版本。在查询的 FROM 子句中引用 CTE 的方式类似于引用派生表和视图的方式。只须定义 CTE 一次，即可在查询中多次引用它。在 CTE 的定义中，可以引用在同一批处理中定义的变量。甚至可以在 INSERT、UPDATE、DELETE 和 CREATE VIEW 语句中以与使用视图类似的方式使用 CTE。CTE 的强大在于它们的递归功能，即可以包含对它们自身的引用。

本文主要是对常见表达式CTE与视图和派生表在SELECT查询中的比较。派生表像引用表一样引用查询结果，而不需要在数据库中创建持久性视图时，但无法只在查询中定义派生表一次然后多次使用它，必须在同一查询中定义多个派生表。而定义CTE一次就可以在查询中多次使用它，而无须在数据库中持续保存它。在同一WITH子句中定义多个 CTE，每一个都引用先前定义的 CT执行非递归查询。CTE根据一个非递归查询作为锚定成员和一个递归查询作为递归成员执行递归查询。

一 视图、派生表和CTE

视图、派生表和CTE的表达形式总结如下：

视图的形式为：

CREATE VIEW () AS

GO SELECT * FROM

派生表的形式为：

SELECT * FROM ()

AS ()

CTE在关键字 WITH 之后，为CTE 提供一个别名，并且为它的结果列提供一个可选的别名列表，编写CTE的主体，然后从外部查询中引用它。如果CTE的WITH子句不是批处理中的第一个语句，则应当通过在它前面放置一个分号 (;) 来将其与前面的语句分隔开。用来避免与WITH子句的其他用法(如用于表提示)混淆。尽管有可能会发现并非在所有情况下都需要包含分号。CTE的形式为：

WITH () AS ()

SELECT * FROM

以下示例显示了如何使用视图、派生表和CTE实现解决方案。工作数据库中的员工表和采购表，每个员工都向经理编号列中指定的经理汇报，员工表中的每个员工都可能在采购表中具有相关的定单。假设希望返回每个员工的定单数量和最后定单日期，并且在同一行中返回经理的类似详细信息。

(一)视图

CREATE VIEW 视图(员工编号,订单总数,最后订单日期)

AS SELECT 员工编号, COUNT(*), MAX(订单日期)

FROM 采购表 GROUP BY 员工编号

GO

SELECT E.员工编号, OE.订单总数, OE.最后订单日期,

E.员工编号, OM.订单总数, OM.最后订单日期

FROM 员工表AS E

JOIN 视图 AS OE

ON E.员工编号= OE.员工编号

LEFT OUTER JOIN 视图AS OM

ON E.经理编号= OM.员工编号

(二)派生表

SELECT E.员工编号, OE.订单总数, OE.最后订单日期,

E.员工编号, OM.订单总数, OM.最后订单日期

FROM 员工表AS E

JOIN (SELECT 员工编号, COUNT(*), MAX(订单日期)

FROM 采购表

GROUP BY 员工编号) AS OE(员工编号, 订单总数, 最后订单日期)

ON E.员工编号= OE.员工编号

LEFT OUTER JOIN

(SELECT 员工编号, COUNT(*), MAX(订单日期)

FROM 采购表

GROUP BY 员工编号) AS OM(员工编号, 订单总数, 最后订单日期)

ON E.经理编号= OM.员工编号

(三)CTE

WITH CTE(员工编号, 订单总数, 最后订单日期)

AS( SELECT 员工编号, COUNT(*), MAX(订单日期)

FROM 采购表 GROUP BY 员工编号)

SELECT E.员工编号, OE.订单总数, OE.最后订单日期,

E.经理编号, OM.订单总数, OM.最后订单日期

FROM 员工表AS E

JOIN CTE AS OE

ON E.员工编号= OE.员工编号

LEFT OUTER JOIN CTE AS OM

ON E.经理编号= OM.员工编号

运行结果如下：

图1 定单信息

二 CTE在非递归中的运用

也可以在同一WITH子句中定义多个 CTE，每一个都引用先前定义的 CTE。逗号用来分隔各个CTE。假设计算员工定单数量的最小值、最大值以及二者之间的差值：

WITH CTE (员工编号, Cnt)

AS( SELECT 员工编号, COUNT(*)

FROM 采购表 GROUP BY 员工编号),

C1(最小值, 最大值, 差异)

AS( SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)

FROM CTE)

SELECT * FROM C1

在员工订单表中，计算每个雇员的定单数量。在差值表C1中，引用员工订单表以计算雇员定单数量的最小值、最大值以及二者之间的差值。以下为结果集：

图2 员工定单数量信息

CTE 内部的引用并非只能引用恰好在它前面定义的 CTE；相反，也可以引用之前定义的所有 CTE。CTE 可以引用在它前面定义的 CTE 和它本身，但是不能引用在它后面定义的 CTE。例如，如果在同一 WITH 语句中定义了 CTE C1、C2、C3，则 C2 可以引用 C1 和 C2，但是不能C3。

示例：计算位于最小值和最大值之间的四个订单数量范围内的员工数量。该示例的目的是使用实际方案来演示如何在同一 WITH 语句中声明多个 CTE(其中每一个都可能引用前面的 CTE)。

WITH CTE(员工编号, Cnt)

AS( SELECT 员工编号, COUNT(*)

FROM 采购表 GROUP BY 员工编号),

C1(最小值, 最大值, 差异)

AS( SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)

FROM CTE),

C2(数值)

AS( SELECT 1 AS 数值

UNION ALL SELECT 2

UNION ALL SELECT 4

UNION ALL SELECT 5),

C3(Step, Fromval, Toval)

AS( SELECT Num,

CAST(最小值+ ROUND((数值-1)*((差异+1)/4.), 0) AS INT),

CAST(最大值+ ROUND((数值)*((差异+1)/4.), 0) - 1 AS INT)

FROM C1 CROSS JOIN C2),

C4(Step, Fromval, Toval, Samples)

AS( SELECT S.Step, S.Fromval, S.Toval, COUNT(员工编号)

FROM C3 AS S

LEFT OUTER JOIN CTE AS OE

ON OE.Cnt BETWEEN S.Fromval AND S.Toval

GROUP BY S.范围, S.Fromval, S.Toval)

SELECT * FROM C4

以下为结果集：

图3 雇员数量信息

第二个 CTE (C1) 引用第一个CTE表；第三个 (C2) 未引用任何 CTE。第四个 (C3) 引用第二个和第三个 CTE，而第五个 (C4) 引用第一个和第四个 CTE。

三 CTE在递归中的运用

非递归CTE 改善了表达能力。但是对于每一段使用非递归 CTE 的代码，通常可以通过使用其他 Transact—SQL 结构(例如，派生表)来编写能够获得相同结果的较短的代码。但是对于递归 CTE，情况是不同的。

当 CTE 引用它本身时，它被视为递归的。递归的 CTE 是根据至少两个查询(或者，用递归查询的说法，为成员)构建的。一个是非递归查询，也称为锚定成员 (AM)。另一个是递归查询，也称为递归成员 (RM)。查询由 UNION ALL 运算符分隔。

以下示例显示了递归 CTE 的简化的一般形式：

WITH RecursiveCTE()

AS( SELECT ... FROM ... —锚定成员 (AM)

UNION ALL

SELECT ... FROM JOIN RecursiveCTE ) —递归成员 (RM)

SELECT ... FROM RecursiveCTE... —出口

在逻辑上，可以将实现递归 CTE 的算法视为：

1) 锚定成员被激活。集 R0(R 表示“结果”)被生成。

2) 递归成员被激活，在引用 RecursiveCTE 时获得集 Ri(i = 步骤号)作为输入。集 Ri + 1 被生成。

3) 步骤 2 的逻辑被反复运行(在每个迭代中递增步骤号)，直到返回空集。

4) 外部查询执行，在引用 RecursiveCTE 时，获得以前所有步骤的累积 (UNION ALL) 结果。

可以在 CTE 中具有两个以上的成员，但是在递归成员和另一个成员(递归或非递归)之间只能有一个 UNION ALL 运算符。其他运算符(例如，UNION)只能在非递归成员之间使用。与支持隐式转换的常规 UNION 和 UNION ALL 运算符不同，递归CTE要求所有成员中的列完全匹配，包括具有相同的数据类型、长度和精度。

在递归 CTE 和传统的递归例程(未必特定于 SQL Server)之间存在相似性。递归例程通常包括三个重要元素，该例程的第一个调用、递归终止检查以及对同一例程的递归调用。递归 CTE 中的锚定成员对应于传统递归例程中该例程的第一个调用。递归成员对应于该例程的递归调用。终止检查在递归例程中通常是显式的(例如，借助于 IF 语句)，但在递归 CTE 中是隐式的，当没有从上一个调用中返回任何行时，递归停止。

本文根据CTE一次定义就可以多次使用的特点，在同一WITH子句中定义多个 CTE，每一个都引用先前定义的 CT执行非递归查询。CTE根据一个非递归查询作为锚定成员和一个递归查询作为递归成员执行递归查询。应用表明，能取得良好的效果。下一步将拓展CTE的应用范围。

参考文献:

[1]汪建, 方洪鹰, 陈昌川. 一种改进的基于数据库的树存储策略[J]. 重庆师范大学学报：自然科学版, 2007,24(4)：50-53.

[2]R. BAEZA-YATES. Modern Information Retrieval[M]. Addison-Wesley, 1999.

[3]HECTOR GARCIA-MOLINA,JEFFREY D. Ullman, Jennife[M].岳丽华,等译.数据库系统全书. 北京：机械工业出版社,2003.

[4]ITZIK B G，SARKA D，WOLTER R. Microsoft SQL Server 2005技术内幕: T-SQL 程序设计[M]. 赵立东，译. 北京: 电子工业出版社，2007．