一个 WITH 子句可以定义多个公用表表达式。这些定义必须用逗号分隔开。以下示例列出了工资额最少的部门和雇员数量最多的部门。
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 AS count JOIN DeptPayroll AS pay ON count.DepartmentID = pay.DepartmentID WHERE count.n = ( SELECT MAX( n ) FROM CountEmployees ) OR pay.amt = ( SELECT MIN( amt ) FROM DeptPayroll ); |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |