使用多个表表达式

一个 WITH 子句可以定义多个公用表表达式。这些定义必须用逗号分隔开。以下示例列出了工资额最少的部门和雇员数量最多的部门。

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 AS count JOIN DepartmentPayroll AS pay
ON count.DepartmentID = pay.DepartmentID
WHERE count.n = ( SELECT MAX( n ) FROM CountEmployees )
   OR pay.amount = ( SELECT MIN( amount ) FROM DepartmentPayroll );

另请参见

使用DocCommentXchange 讨论此页。