公用表表达式

公用表表达式是使用 WITH 子句定义的，该子句在 SELECT 语句中的 SELECT 关键字之前。子句的内容定义了仅在单个 SELECT 语句范围内已知并且可能会在语句中的其它位置引用的一个或多个临时视图。此子句的语法模拟 CREATE VIEW 语句的语法。

如果查询涉及到多个集合函数，或者用于在存储过程中定义一个引用程序变量的视图，那么，公用表表达式就会很有用，并且可能是必需的。公用表表达式还提供了一种便捷的方式，用以临时存储值集。

示例

如，以确定哪个部门的雇员数量最多的问题为例。SQL Anywhere 示例数据库中的 Employees 表列出了一家虚构公司内的所有雇员，并指定了每名雇员的工作部门。以下查询列出部门 ID 代码以及各部门中的雇员总数。

SELECT DepartmentID, COUNT( * ) AS n
FROM Employees
GROUP BY DepartmentID;

此查询可用于提取雇员最多的部门，如下所示：

SELECT DepartmentID, n
FROM ( SELECT DepartmentID, COUNT( * ) AS n
       FROM Employees GROUP BY DepartmentID ) AS a
WHERE a.n =
  ( SELECT MAX( n )
    FROM ( SELECT DepartmentID, COUNT( * ) AS n
           FROM Employees GROUP BY DepartmentID ) AS b );

虽然此语句提供了正确的结果，但它有几个缺点。第一个缺点是重复的子查询会降低此语句的效率。第二个缺点是此语句没有在子查询之间提供清楚的链接。

解决这些问题的一个方法是创建一个视图，然后使用该视图重新表达查询。这种方法可避免上述问题。

CREATE VIEW CountEmployees( DepartmentID, n ) AS
   SELECT DepartmentID, COUNT( * ) AS n
   FROM Employees GROUP BY DepartmentID; 

SELECT DepartmentID, n
   FROM CountEmployees
   WHERE n = ( SELECT MAX( n )
               FROM CountEmployees );

这种方法的缺点是需要一些开销，因为数据库服务器必须在创建视图时更新系统表。如果经常使用该视图，则使用这种方法是合理的。但是，如果只在特定 SELECT 语句中使用一次该视图，则首选方法是改为使用公用表表达式，如下所示。

WITH CountEmployees( DepartmentID, n ) AS
  ( SELECT DepartmentID, COUNT( * ) AS n
    FROM Employees GROUP BY DepartmentID )
SELECT DepartmentID, n
FROM CountEmployees
WHERE n = ( SELECT MAX( n )
            FROM CountEmployees );

将查询改为搜索雇员最少的部门说明这种查询可以返回多个行。

WITH CountEmployees( DepartmentID, n ) AS
  ( SELECT DepartmentID, COUNT( * ) AS n
    FROM Employees GROUP BY DepartmentID )
SELECT DepartmentID, n
FROM CountEmployees
WHERE n = ( SELECT MIN( n )
            FROM CountEmployees );

在 SQL Anywhere 示例数据库中，有两个部门的雇员数最少，均为 9。

另请参见

使用DocCommentXchange讨论此页。