iAnywhere.com - 公用表表达式的典型应用

ASA SQL 用户指南

公用表表达式

公用表表达式的典型应用

一般而言，只要表的表达式必须在一个查询中出现多次，公用表表达式就很有用。下面的典型情况适合于公用表表达式。

涉及多个集合函数的查询。
过程中必须包含对程序变量的引用的视图。
使用临时视图存储一组值的查询。

此列表并不完整。您可能会遇到许多其它要使用公用表表达式的情况。

多个集合函数

只要必须在一个查询中显示多个级别的集合，公用表表达式就很有用。这是上一部分使用的示例中的情况。任务是检索雇员数量最多的部门的部门 ID。为此，要使用 count 集合函数来计算每个部门的雇员数量，并使用 max 函数选择最大的部门。

在编写查询来确定哪个部门的工资额最多时，会出现类似的情况。sum 集合函数用于计算每个部门的工资额，而 max 函数用于确定哪个部门最大。查询中同时出现这两个函数表明公用表表达式可能有用。

WITH DeptPayroll( dept_id, amt ) AS
    ( SELECT dept_id, sum(salary) AS amt
      FROM employee GROUP BY dept_id )
SELECT dept_id, amt
FROM DeptPayroll
WHERE amt = ( SELECT max(amt)
              FROM DeptPayroll )

引用程序变量的视图

有时，公用表表达式对创建包含对程序变量的引用的视图可能会很方便。例如，您可以定义过程中标识特定客户的变量。您要查询该客户的购买历史记录，并且如果您要多次访问类似信息或者可能会使用多个集合函数，则您需要创建一个包含有关该特定客户的信息的视图。

您无法创建引用程序变量的视图，因为无法将视图范围限制为您的过程的范围。一旦创建了视图，就可以在其它环境中使用它。但是，您可以在您的过程中的查询内使用公用表表达式。因为公用表表达式的范围限制到语句，所以变量引用不会造成任何多义性，因此可以使用变量引用。

下面的语句在示例数据库中选择各位不同销售代表的销售总额。

SELECT emp_fname || ' ' || emp_lname AS sales_rep_name,
       sales_rep AS sales_rep_id,
       sum( p.unit_price * i.quantity ) AS total_sales
FROM employee LEFT OUTER JOIN sales_order AS o
              INNER JOIN sales_order_items AS i
              INNER JOIN product AS p
WHERE '2000-01-01' <= order_date
                  AND order_date < '2001-01-01'
GROUP BY sales_rep, emp_fname, emp_lname

上面的查询是出现在下面过程中的公用表表达式的基础。销售代表的 ID 号和所讨论的年份是即将使用的参数。如此过程所示，可在 WITH 子句中引用过程参数和任何已声明的局部变量。

CREATE PROCEDURE sales_rep_total (
  IN rep  INTEGER,
  IN yyyy INTEGER )
BEGIN
  DECLARE start_date DATE;
  DECLARE end_date   DATE;
  SET start_date = YMD( yyyy,  1,  1 );
  SET   end_date = YMD( yyyy, 12, 31 );
  WITH total_sales_by_rep ( sales_rep_name,
                            sales_rep_id,
                            month,
                            order_year,
                            total_sales ) AS
  ( SELECT emp_fname || ' ' || emp_lname AS sales_rep_name,
           sales_rep AS sales_rep_id, month( order_date),
           year(order_date),
           sum( p.unit_price * i.quantity ) AS total_sales
    FROM employee LEFT OUTER JOIN sales_order o
                       INNER JOIN sales_order_items i
                       INNER JOIN product p
    WHERE start_date <= order_date AND
                        order_date <= end_date AND
          sales_rep = rep
    GROUP BY year(order_date), month(order_date),
             emp_fname, emp_lname, sales_rep )
  SELECT sales_rep_name,
            monthname( YMD(yyyy, month, 1) ) AS month_name,
            order_year,
            total_sales
  FROM total_sales_by_rep
  WHERE total_sales =
    ( SELECT max( total_sales) FROM total_sales_by_rep )
  ORDER BY order_year ASC, month ASC;
END;

下面的语句说明了如何调用上面的过程。

CALL sales_rep_total(129, 2000);

存储值的视图

有时，公用表表达式在某个 SELECT 语句或某个过程中存储一组特定的值会很有用。例如，假定一家公司要按三分之一年度而不是按季度分析它的销售人员的结果。由于没有代表三分之一的内置日期部分（虽然有代表季度的内置日期部分），所以有必要将这些日期存储在过程中。

WITH thirds (q_name, q_start, q_end) AS
( SELECT 'T1', '2000-01-01', '2000-04-30' UNION
  SELECT 'T2', '2000-05-01', '2000-08-31' UNION
  SELECT 'T3', '2000-09-01', '2000-12-31' )
SELECT q_name,
       sales_rep,
       count(*) AS num_orders,
       sum( p.unit_price * i.quantity ) AS total_sales
FROM thirds LEFT OUTER JOIN sales_order AS o
    ON q_start <= order_date AND order_date <= q_end
                   INNER JOIN sales_order_items AS i
                   INNER JOIN product AS p
 GROUP BY q_name, sales_rep
 ORDER BY q_name, sales_rep

使用此方法时应该小心，因为值可能需要定期维护。例如，如果要为任何其它年度使用上面的语句，则必须对它进行修改。

您还可以在过程中应用此技术。下面的示例声明了一个过程，该过程将所讨论的年份作为参数。

CREATE PROCEDURE sales_by_third ( IN y INTEGER )
BEGIN
  WITH thirds (q_name, q_start, q_end) AS
  ( SELECT 'T1', YMD( y, 01, 01), YMD( y, 04, 30) UNION
    SELECT 'T2', YMD( y, 05, 01), YMD( y, 08, 31) UNION
    SELECT 'T3', YMD( y, 09, 01), YMD( y, 12, 31) )
  SELECT q_name,
         sales_rep,
         count(*) AS num_orders,
         sum(p.unit_price * i.quantity) AS total_sales
  FROM thirds JOIN sales_order AS o
    ON q_start <= order_date AND order_date <= q_end
            KEY JOIN sales_order_items AS i
            KEY JOIN product AS p
  GROUP BY q_name, sales_rep
  ORDER BY q_name, sales_rep;
END;

CALL sales_by_third (2000);