Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
SQL Anywhere 12.0.0 (中文) » SQL Anywhere 服务器 - SQL 的用法 » 查询和修改数据 » 公用表表达式

 

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

虽然可以在整个查询主体或任何子查询中引用公用表表达式定义，但只允许在三个位置使用它们。

  • 顶级 SELECT 语句   允许在顶级 SELECT 语句中使用公用表表达式，但不允许在子查询中使用。 
    WITH DepartmentPayroll( DepartmentID, amount ) AS
  ( SELECT DepartmentID, SUM( Salary ) AS amount
    FROM Employees GROUP BY DepartmentID )
SELECT DepartmentID, amount
FROM DepartmentPayroll
WHERE amount = ( SELECT MAX( amount )
              FROM DepartmentPayroll );

  • 视图定义中的顶级 SELECT 语句   允许在定义视图的顶级 SELECT 语句中使用公用表表达式，但不允许在定义中的子查询中使用。 


    CREATE VIEW LargestDept ( DepartmentID, Size, pay ) AS
   WITH
     CountEmployees( DepartmentID, n ) AS
       ( SELECT DepartmentID, COUNT( * ) AS n
         FROM Employees GROUP BY DepartmentID ),
     DepartmentPayroll( DepartmentID, amount ) AS
       ( SELECT DepartmentID, SUM( Salary ) AS amount
         FROM Employees GROUP BY DepartmentID )
  SELECT count.DepartmentID, count.n, pay.amount
  FROM CountEmployees count JOIN DepartmentPayroll pay
  ON count.DepartmentID = pay.DepartmentID
  WHERE count.n = ( SELECT MAX( n ) FROM CountEmployees )
     OR pay.amount = ( SELECT MAX( amount ) FROM DepartmentPayroll );

  • INSERT 语句中的顶级 SELECT 语句   允许在 INSERT 语句中的顶级 SELECT 语句中使用公用表表达式，但不允许在 INSERT 语句中的子查询中使用。 


    CREATE TABLE LargestPayrolls ( DepartmentID INTEGER, Payroll NUMERIC, CurrentDate DATE );
INSERT INTO LargestPayrolls( DepartmentID, Payroll, CurrentDate )
  WITH DepartmentPayroll( DepartmentID, amount ) AS
    ( SELECT DepartmentID, SUM( Salary ) AS amount
      FROM Employees
      GROUP BY DepartmentID )
  SELECT DepartmentID, amount, CURRENT TIMESTAMP
  FROM DepartmentPayroll
  WHERE amount = ( SELECT MAX( amount )
                FROM DepartmentPayroll );

 另请参见
版权 © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0