存储值的视图

这对于在某个 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,
       SalesRepresentative,
       count(*) AS num_orders,
       SUM( p.UnitPrice * i.Quantity ) AS total_sales
FROM thirds LEFT OUTER JOIN SalesOrders AS o
    ON OrderDate BETWEEN q_start and q_end
                   KEY JOIN SalesOrderItems AS I
                   KEY JOIN Products AS p
 GROUP BY q_name, SalesRepresentative
 ORDER BY q_name, SalesRepresentative;

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

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

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,
         SalesRepresentative,
         count(*) AS num_orders,
         SUM( p.UnitPrice * i.Quantity ) AS total_sales
  FROM thirds LEFT OUTER JOIN SalesOrders AS o
    ON OrderDate BETWEEN q_start and q_end
            KEY JOIN SalesOrderItems AS I
            KEY JOIN Products AS p
  GROUP BY q_name, SalesRepresentative
  ORDER BY q_name, SalesRepresentative;
END;

以下语句调用上述过程。

CALL sales_by_third (2000);

使用DocCommentXchange讨论此页。