SQL：SQLite可辨识的语言
【置顶】
WITH 从句
with-clause: 隐藏
cte-table-name: 展示
select-stmt: 展示
common-table-expression: 展示
compound-operator: 展示
expr: 展示
literal-value: 展示
raise-function: 展示
type-name: 展示
signed-number: 展示
join-clause: 展示
join-constraint: 展示
join-operator: 展示
ordering-term: show
result-column: 展示
table-or-subquery: 展示
公共表表达式或CTE的作用类似于只存在于单个SQL语句期间的临时视图。共有两种表格表达式：“普通”和“递归”。普通的公用表表达式有助于通过将子查询分解为主SQL语句以使查询更容易理解。递归公用表表达式提供了对树和图进行分层或递归查询的能力，这种功能在SQL语言中是不可用的。
所有公用表表达式（普通表达式和递归表达式）都是通过在SELECT，INSERT，DELETE或UPDATE语句前加上WITH子句来创建的。单个WITH子句可以指定一个或多个公用表表达式，其中一些是普通表，其中一些是普通表，其中一些是递归的。
一般型公用表表达式
一般公用表表达式的工作原理就好像它是在单个语句期间存在的视图一样，可用于分解子查询并使整个SQL语句更易于阅读和理解。
WITH子句可以包含一般公用表表达式，即使它包含RECURSIVE关键字。RECURSIVE的使用不会强制公用表表达式递归。
递归公用表表达式
递归公用表表达式可用于编写遍历树或图的查询。递归公用表表达式具有与普通公用表表达式相同的基本语法，但具有以下附加功能：
- “select-stmt”必须是复合选择，其中最右侧的复合运算符是UNION或UNION ALL。
- 在AS关键字左侧命名的表必须在复合选择的最右侧SELECT语句的FROM子句中恰好出现一次，而在其他地方不会出现。
换句话说，递归公用表表达式必须如下所示：
recursive-cte: 隐藏
cte-table-name: 展示
在递归公用表表达式“recursive table”中调用由cte-table-name命名的表。在上面的递归cte气泡图中，递归表必须在递归选择的FROM子句中出现一次，并且不能出现在初始选择或递归选择中的任何其他位置，包括子查询。初始选择可能是复合选择，但可能不包括ORDER BY，LIMIT或OFFSET。递归选择必须是简单的选择，而不是化合物。递归选择允许包含ORDER BY，LIMIT和/或OFFSET。
计算递归表格内容的基本算法如下：
- 运行初始选择并将结果添加到队列中。
-
虽然队列不是空的：
- 从队列中提取单个行。
- 将该单行插入递归表中
- 假设刚刚提取的单个行是递归表中的唯一行，并运行递归选择，将所有结果添加到队列中。
上述基本程序可以通过以下附加规则进行修改：
- 如果UNION操作符连接初始选择和递归选择，则只有在队列中没有添加相同的行时，才将行添加到队列中。重复行在被添加到队列之前被丢弃，即使重复行已被递归步骤从队列中提取出来。如果运算符是UNION ALL，则初始选择和递归选择生成的所有行都始终添加到队列中，即使它们是重复的也是如此。当确定一个行是否重复时，NULL值彼此相等并且不等于任何其他值。
- LIMIT子句（如果存在）确定将在步骤2b中添加到递归表的最大行数。达到限制后，递归停止。限制为零意味着不会将行添加到递归表中，而负限制意味着可以将无限数量的行添加到递归表中。
- OFFSET子句如果存在且具有正值N，则会阻止将前N行添加到递归表中。前N行仍由递归选择处理 - 它们不会被添加到递归表中。在所有OFFSET行被跳过之前，行不会被计入满足LIMIT的限制。
- 如果存在ORDER BY子句，它将确定在步骤2a中从队列中提取行的顺序。如果没有ORDER BY子句，则提取行的顺序未定义。（在当前的实现中，如果ORDER BY子句被省略，则队列变为FIFO，但应用程序不应该依赖于该事实，因为它可能会改变。）
递归查询示例
以下查询返回1到1000000之间的所有整数：
WITH RECURSIVE
cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000000)
SELECT x FROM cnt;考虑这个查询如何工作。初始选择首先运行，并返回带有单列“1”的单行。这一行被添加到队列中。在步骤2a中，从队列中提取一行并添加到“cnt”。然后按照步骤2c运行递归选择，生成值为“2”的单个新行以添加到队列中。队列仍然有一行，所以步骤2重复。通过步骤2a和2b提取“2”行并将其添加到递归表中。然后使用包含2的行，就好像它是递归表的完整内容，并且递归选择再次运行，导致将值为“3”的行添加到队列中。这重复999999次直到最后在步骤2a，队列上的唯一值是包含1000000的行。该行被提取并添加到递归表中。但是这一次，WHERE子句导致递归选择不返回任何行，所以队列保持为空，递归停止。
优化笔记： 在上面的讨论中，像“将行插入递归表”这样的语句应该从概念上理解，而不是字面意思。这听起来好像SQLite正在积累一个包含一百万行的巨大表，然后返回并从上到下扫描该表以生成结果。真正发生的是，查询优化器发现“cnt”递归表中的值只能使用一次。因此，当每行添加到递归表中时，该行将立即作为主SELECT语句的结果返回，然后被丢弃。SQLite 没有 累积一个包含一百万行的临时表。运行上述示例所需的内存很少。但是，如果示例使用了UNION而不是UNION ALL，那么SQLite将不得不保留所有以前生成的内容以检查重复项。出于这个原因，程序员应该努力在可行的时候使用UNION ALL而不是UNION。