ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [转载]公用表表达式的典型应用

[转载]公用表表达式的典型应用

原创 Linux操作系统 作者:NinGoo 时间:2019-02-13 19:36:04 0 删除 编辑

转载自ASA SQL用户指南

公用表表达式(CTE):Common Table Expression

使用 SELECT 语句的 WITH 前缀,您可以定义公用表表达式。您可以像使用临时视图一样在查询中使用这些公用表表达式。

===============================================================

NinGoo:本文虽然是基于Sybase的,但是公用表表达式在Oracle中的使用和语法基本相同,但是Oracle中的公用表表达式不支持列别名,也就是如下的语法在Sybase中是合法的,在oracle中会报错:

with test_table(col1,col2)
as (select col1,col2 from test)
select * from test_table;

ORA-32033: 不支持列别名

所以,在oracle中,语法如下:

with test_table
as (select col1,col2 from test)
select * from test;

MS SQL Server2005也支持CTE:http://msdn2.microsoft.com/zh-cn/library/ms190766.aspx

==============================================================


公用表表达式是使用 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 名)的雇员。

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

  • 涉及多个集合函数的查询。

  • 过程中必须包含对程序变量的引用的视图。

  • 使用临时视图存储一组值的查询。

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

多个集合函数

只要必须在一个查询中显示多个级别的集合,公用表表达式就很有用。这是上一部分使用的示例中的情况。任务是检索雇员数量最多的部门的部门 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);

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/193161/viewspace-50222/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    200
  • 访问量
    134626