iAnywhere.com - 关于公用表表达式

关于公用表表达式

公用表表达式是临时视图，它们只在某一条 SELECT 语句的范围内是已知的。使用它们，您编写查询会更容易，您还可以编写无法以其它方式表达的查询。

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

使用递归公用表表达式，您可以查询呈现分层信息（例如，公司内的隶属关系）的表。使用它们，还可以解决部件激增问题和最短距离问题。

有关递归查询的信息，请参见递归公用表表达式。

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

SELECT dept_id, count(*) AS n
FROM employee
GROUP BY dept_id

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

SELECT dept_id, n
FROM ( SELECT dept_id, count(*) AS n
       FROM employee GROUP BY dept_id ) AS a
WHERE a.n =
  ( SELECT max(n)
    FROM ( SELECT dept_id, count(*) AS n
           FROM employee GROUP BY dept_id ) AS b )

虽然此语句提供了正确的结果，但它有一些缺点。第一个缺点是重复的子查询使得此语句非常笨拙。第二是此语句没有在子查询之间提供清楚的链接。

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

CREATE VIEW CountEmployees(dept_id, n) AS
    SELECT dept_id, count(*) AS n
    FROM employee GROUP BY dept_id;

SELECT dept_id, n
FROM CountEmployees
WHERE n = ( SELECT max(n)
            FROM CountEmployees );

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

使用公用表表达式

公用表表达式是使用 WITH 子句定义的，该子句在 SELECT 语句中的 SELECT 关键字前面。子句的内容定义了之后可能会在语句中的其它位置引用的一个或多个临时视图。此子句的语法模拟 CREATE VIEW 语句的语法。您可以使用公用表表达式来表达上一个查询，如下所示。

WITH CountEmployees(dept_id, n) AS
  ( SELECT dept_id, count(*) AS n
    FROM employee GROUP BY dept_id )
SELECT dept_id, n
FROM CountEmployees
WHERE n = ( SELECT max(n)
            FROM CountEmployees )

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

WITH CountEmployees(dept_id, n) AS
  ( SELECT dept_id, count(*) AS n
    FROM employee GROUP BY dept_id )
SELECT dept_id, n
FROM CountEmployees
WHERE n = ( SELECT min(n)
            FROM CountEmployees )

在示例数据库中，两个部门共享着最少数量（即 9 名）的雇员。

多个相关名

就像在使用表时一样，您可以为公用表表达式的多个实例指定不同的相关名。这样做使您可以将公用表表达式与它本身连接。例如，下面的查询生成了具有相同数量雇员的部门对，虽然示例数据库中只有两个具有相同数量雇员的部门。

WITH CountEmployees(dept_id, n) AS
    ( SELECT dept_id, count(*) AS n
      FROM employee GROUP BY dept_id )
SELECT a.dept_id, a.n, b.dept_id, b.n
FROM CountEmployees AS a JOIN CountEmployees AS b
ON a.n = b.n AND a.dept_id < b.dept_id

多个表的表达式

一个 WITH 子句可以定义多个公用表表达式。这些定义必须用逗号分隔开。下面的示例列出了工资额最少的部门和雇员数量最多的部门。

WITH
  CountEmployees(dept_id, n) AS
    ( SELECT dept_id, count(*) AS n
      FROM employee GROUP BY dept_id ),
  DeptPayroll( dept_id, amt ) AS
     ( SELECT dept_id, sum(salary) AS amt
       FROM employee GROUP BY dept_id )
SELECT count.dept_id, count.n, pay.amt
FROM CountEmployees AS count JOIN DeptPayroll AS pay
ON count.dept_id = pay.dept_id
WHERE count.n = ( SELECT max(n) FROM CountEmployees )
   OR pay.amt = ( SELECT min(amt) FROM DeptPayroll )

允许使用公用表表达式的位置