共通テーブル式はクエリ本体または任意のサブクエリ内から参照可能ですが、共通テーブル式を定義できるのは 3 か所のみです。
最上位レベルの 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 ); |
| Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |