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

公用表表达式在数据查询中应用 　　摘 要 在数据库查询中使用公共表达式可以使得代码层次结构更加清晰，可读性更强。下面以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.Max