Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (中文) » SQL Anywhere 服务器 - SQL 的用法 » 查询和修改数据 » 公用表表达式

 

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

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

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

  • 视图定义中的顶级 SELECT 语句   允许在定义视图的顶级 SELECT 语句中使用公用表表达式,但不允许在定义中的子查询中使用。
    CREATE VIEW LargestDept ( DepartmentID, Size, pay ) AS
       WITH
         CountEmployees( DepartmentID, n ) AS
           ( SELECT DepartmentID, COUNT( * ) AS n
             FROM Employees GROUP BY DepartmentID ),
         DeptPayroll( DepartmentID, amt ) AS
           ( SELECT DepartmentID, SUM( Salary ) AS amt
             FROM Employees GROUP BY DepartmentID )
      SELECT count.DepartmentID, count.n, pay.amt
      FROM CountEmployees count JOIN DeptPayroll pay
      ON count.DepartmentID = pay.DepartmentID
      WHERE count.n = ( SELECT MAX( n ) FROM CountEmployees )
         OR pay.amt = ( SELECT MAX( amt ) FROM DeptPayroll );

  • INSERT 语句中的顶级 SELECT 语句   允许在 INSERT 语句中的顶级 SELECT 语句中使用公用表表达式,但不允许在 INSERT 语句中的子查询中使用。
    CREATE TABLE LargestPayrolls ( DepartmentID INTEGER, Payroll NUMERIC, CurrentDate DATE );
    INSERT INTO LargestPayrolls( DepartmentID, Payroll, CurrentDate )
      WITH DeptPayroll( DepartmentID, amt ) AS
        ( SELECT DepartmentID, SUM( Salary ) AS amt
          FROM Employees
          GROUP BY DepartmentID )
      SELECT DepartmentID, amt, CURRENT TIMESTAMP
      FROM DeptPayroll
      WHERE amt = ( SELECT MAX( amt )
                    FROM DeptPayroll );

另请参见