Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
SQL Anywhere 12.0.1 » SQL Anywhere サーバー SQL の使用法 » データの問い合わせと修正 » 共通テーブル式

 

共通テーブル式を使用できる条件

共通テーブル式はクエリ本体または任意のサブクエリ内から参照可能ですが、共通テーブル式を定義できるのは 3 か所のみです。

  • 最上位レベルの 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 );

 参照
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1