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

SAP Sybase SQL Anywhere 16.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 );

 另请参见