公用表表达式在数据查询中的应用

摘 要 在数据库查询中使用公共表达式可以使得代码层次结构更加清晰，可读性更强。下面以SQLSERVER 2008为例简要公共表达式的语法和用法。

关键词 SQL语句 数据查询 公用表表达式

中图分类号：TP312.2 文献标识码：A

公共表表达式可以指定临时命名的结果集，这些结果集称为公用表表达式 （CTE）。该表达式源自简单查询，并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中，作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。

1语法结构

[ WITH [ ，...n ] ]

：：=

expression_name？[ （ column_name [ ，...n ] ） ]

AS

（ CTE_query_definition ）

其中，expression_name 未公用表表达式的有效标识符。 expression_name 必须与在同一 WITH 子句中定义的任何其他公用表表达式的名称不同，但 expression_name 可以与基表或基视图的名称相同。在查询中对 expression_name 的任何引用都会使用公用表表达式，而不使用基对象。column_name在公用表表达式中指定列名。在一个 CTE 定义中不允许出现重复的名称。指定的列名数必须与 CTE_query_definition 结果集中列数匹配。只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。CTE_query_definition 指定一个其结果集填充公用表表达式的 SELECT 语句。除了 CTE 不能定义另一个 CTE 以外，CTE_query_definition 的 SELECT 语句必须满足与创建视图时相同的要求。

2创建和使用公用表表达式的准则

创建和使用公用表表达式必须遵循下面准则，否则运行查询代码会提示错误。

（1）CTE 之后必须跟随引用部分或全部 CTE 列的单条 SELECT、INSERT、UPDATE 或 DELETE 语句。也可以在 CREATE VIEW 语句中将 CTE 指定为视图中 SELECT 定义语句的一部分。可以在非递归 CTE 中定义多个 CTE 查询定义。定义必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。

（2）不允许在一个 CTE 中指定多个 WITH 子句。例如，如果 CTE_query_definition 包含一个子查询，则该子查询不能包括定义另一个 CTE 的嵌套的 WITH 子句。

（3）如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾。

3简单公用表表达式使用示例

以下示例@示直接向 Adventure Works Cycles 的每个经理报告的雇员的数目。

WITH DirReps（ManagerID， DirectReports） AS

（

SELECT ManagerID， COUNT（*）

FROM HumanResources.Employee AS e

WHERE ManagerID IS NOT NULL

GROUP BY ManagerID

）

SELECT ManagerID， DirectReports

FROM DirReps

ORDER BY ManagerID；

以下示例显示 SalesOrderHeader 表中每个销售人员的销售订单的总数和最近的销售订单的日期。CTE 在运行的语句中被引用两次：一次返回为销售人员所选的列，另一次检索销售经理的类似详细信息。销售人员和销售经理的数据都返回在一行中。

WITH Sales_CTE （SalesPersonID， NumberOfOrders， MaxDate）

AS

（

SELECT SalesPersonID， COUNT（*）， MAX（OrderDate）

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

）

SELECT E.EmployeeID， OS.NumberOfOrders， OS.MaxDate，

E.ManagerID， OM.NumberOfOrders， OM.MaxDate

FROM HumanResources.Employee AS E

JOIN Sales_CTE AS OS

ON E.EmployeeID = OS.SalesPersonID

LEFT OUTER JOIN Sales_CTE AS OM

ON E.ManagerID = OM.SalesPersonID

ORDER BY E.EmployeeID；

参考文献

[1] 杜佰林.网络数据库SQL Server 2000[M].清华出版社，2009，09.

[2] 何薇，舒后.网络数据库技术与应用[M].清华大学出版社，2014，10.