《SQL 烹饪书》第二版

附录 B. 常用表格表达式

本手册中介绍的许多查询都超出了使用数据库中通常可用的表所能实现的范围，尤其是与聚合函数和窗口函数有关的查询。因此，对于某些查询，需要使用派生表--子查询或通用表表达式 (CTE)。

子查询

可以说，创建虚拟表以允许对窗口函数或聚合函数进行查询的最简单方法就是子查询。只需在括号中写入所需的查询，然后写入第二个使用该查询的查询即可。下表通过一个简单的双汇总表说明了子查询的使用方法--我们不仅要查找每个工作中的员工人数，还要找出最高人数，但我们无法在标准查询中直接嵌套汇总函数。

一个陷阱是，有些供应商要求你提供子查询表和别名，但有些则不需要。下面的示例是在 MySQL 中编写的，它确实需要别名。这里的别名是 HEAD_COUNT_TAB 后的括号。

需要使用别名的还有 PostgreSQL 和 SQL Server，而 Oracle 则不需要：

select max(HeadCount) as HighestJobHeadCount from
(select job,count(empno) as HeadCount
from emp
group by job) head_count_tab

常用表格表达式

CTE 的目的是克服子查询的一些限制，最著名的可能是允许在 SQL 中使用递归查询。事实上，在 SQL 中实现递归是 CTE 的主要灵感来源。

这个示例的结果与我们前面看到的子查询相同，都是找到一个双聚合：

with head_count_tab (job,HeadCount) as

(select job,count(empno)
from emp
group by job)

select max(HeadCount) as HighestJobHeadCount
from head_count_tab

虽然该查询解决的是一个简单问题，但它说明了 CTE 的基本特征。我们使用 WITH 子句引入派生表，在括号中指定列标题，并在派生表的查询本身周围使用括号。如果我们想添加更多派生表，只要用逗号分隔每个派生表，并在其查询之前提供其名称即可（这与 SQL 中通常使用别名的方式相反）。

由于内部查询是在外部查询之前提出的，因此在很多情况下，内部查询也会被认为更具可读性--它们可以让我们更容易地分别研究查询的每个逻辑元素，从而理解逻辑流程。当然，就像编码中的所有事情一样，这也会因情况而异，有时子查询会更具可读性。

考虑到递归是 CTE 存在的关键原因，展示其能力的最佳方式就是通过递归查询。

下面的查询使用递归 CTE 计算斐波那契数列中的前 20 个数字。请注意，在锚查询的第一部分，我们可以初始化虚拟表第一行中的值：

with recursive workingTable ( fibNum, NextNumber, index1)
as
(select 0,1,1
union all
select fibNum+nextNumber,fibNUm,index1+1
from anchor
where index1<20)

select fibNum from workingTable as fib

斐波那契数列通过将当前数字和前一个数字相加来找到下一个数字；您也可以使用 LAG 来实现这一结果。不过，在本例中，我们使用了两列来计算当前数字和前一个数字，从而实现了伪 LAG。请注意关键字 RECURSIVE，它在 MySQL、Oracle 和 PostgreSQL 中是强制性的，但在 SQL Server 或 DB2 中不是。在此查询中，index1 ...