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

SQL Anywhere 12.0.0 (中文) » SQL Anywhere 服务器 - SQL 的用法 » 查询和修改数据 » 公用表表达式



就像在使用表时一样,您可以为公用表表达式的多个实例指定不同的相关名。这样做使您可以将公用表表达式与它本身连接。例如,下面的查询生成了具有相同数量雇员的部门对,虽然 SQL Anywhere 示例数据库中只有两个具有相同数量雇员的部门。

WITH CountEmployees( DepartmentID, n ) AS
    ( SELECT DepartmentID, COUNT( * ) AS n
      FROM Employees GROUP BY DepartmentID )
SELECT a.DepartmentID, a.n, b.DepartmentID, b.n
FROM CountEmployees AS a JOIN CountEmployees AS b
ON a.n = b.n AND a.DepartmentID < b.DepartmentID;