小巧、快速、可靠。
三者选其二。
三者选其二。
公用表表达式或 CTE 就像只在单个 SQL 语句执行期间存在的临时 视图。公用表表达式有两种类型：“普通”和“递归”。普通公用表表达式有助于通过将子查询从主 SQL 语句中分离出来使查询更容易理解。递归公用表表达式提供了对树和图进行层次或递归查询的能力，而这种能力在 SQL 语言中是不可用的。
所有公用表表达式（普通和递归）都是通过在 SELECT、INSERT、DELETE 或 UPDATE 语句之前添加 WITH 子句来创建的。单个 WITH 子句可以指定一个或多个公用表表达式，其中一些是普通的，另一些是递归的。
普通公用表表达式的工作方式就像一个在单个语句执行期间存在的 视图。普通公用表表达式对于分离子查询并使整个 SQL 语句更易于阅读和理解非常有用。
WITH 子句即使包含 RECURSIVE 关键字，也可以包含普通公用表表达式。RECURSIVE 的使用不会强制公用表表达式为递归。
递归公用表表达式可用于编写遍历树或图的查询。递归公用表表达式与普通公用表表达式具有相同的基本语法，但具有以下额外属性
换句话说，递归公用表表达式必须类似于以下内容
在上面的图表中，initial-select 表示一个或多个非递归 SELECT 语句，而 recursive-select 表示一个或多个递归 SELECT 语句。最常见的情况是只有一个 initial-select 和一个 recursive-select，但允许多个。
在递归公用表表达式中，将由 cte-table-name 命名的表称为“递归表”。在上面的 recursive-cte 泡泡图中，递归表必须在 recursive-select 中每个顶级 SELECT 语句的 FROM 子句中恰好出现一次，并且不能在 initial-select 或 recursive-select 中的任何其他地方出现，包括子查询。 initial-select 可以是 复合选择，但它可能不包括 ORDER BY、LIMIT 或 OFFSET。 recursive-select 也可以是 复合选择，限制是该复合语句的所有元素必须使用与 initial-select 和 recursive-select 分隔相同的 UNION 或 UNION ALL 运算符分隔。 recursive-select 允许包含 ORDER BY、LIMIT 和/或 OFFSET，但可能不使用 聚合函数 或 窗口函数。
在 版本 3.34.0（2020-12-01）中添加了 recursive-select 为复合语句的能力。在 SQLite 的早期版本中，recursive-select 只能是单个简单的 SELECT 语句。
计算递归表内容的基本算法如下
上述基本过程可能会被以下附加规则修改
如果 UNION 运算符将 initial-select 与 recursive-select 连接起来，那么只有在队列中没有添加过相同行时才将行添加到队列中。重复的行在添加到队列之前会被丢弃，即使这些重复的行已经从队列中提取出来。如果运算符是 UNION ALL，那么由 initial-select 和 recursive-select 生成的所有行都会始终添加到队列中，即使它们是重复的。在确定一行是否重复时，NULL 值彼此相等，而与任何其他值不相等。
如果存在 LIMIT 子句，则它会决定在步骤 2b 中添加到递归表中的最大行数。达到限制后，递归停止。限制为零意味着没有行会添加到递归表中，而负限制意味着可以将无限数量的行添加到递归表中。
如果存在 OFFSET 子句且具有正值 N，则会阻止将前 N 行添加到递归表中。前 N 行仍会由 recursive-select 处理，只是它们不会添加到递归表中。直到跳过所有 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;
考虑一下这个查询是如何工作的。initial-select 首先运行，并返回一个包含一个名为“1”的列的单行。该行被添加到队列中。在步骤 2a 中，该行从队列中提取并添加到“cnt”中。然后根据步骤 2c 运行 recursive-select，生成一个包含值“2”的新行，添加到队列中。队列仍然包含一行，因此步骤 2 重复。包含 2 的行由步骤 2a 和 2b 提取并添加到递归表中。然后将包含 2 的行用作递归表的完整内容，并再次运行 recursive-select，导致包含值“3”的行被添加到队列中。这会重复 999999 次，直到最后在步骤 2a 中，队列中唯一的值是一行包含 1000000。该行被提取并添加到递归表中。但这一次，WHERE 子句导致 recursive-select 不返回任何行，因此队列保持为空，递归停止。
优化说明：在上面的讨论中，诸如“将该行插入到递归表中”之类的语句应该在概念上理解，而不是字面上理解。听起来好像 SQLite 正在积累一个包含一百万行的巨大表，然后从头到尾扫描该表以生成结果。实际上发生的是，查询优化器会发现“cnt”递归表中的值只使用一次。因此，随着每一行被添加到递归表中，该行会立即作为主 SELECT 语句的结果返回，然后被丢弃。SQLite 不会积累一个包含一百万行的临时表。运行上述示例所需的内存很少。但是，如果示例使用 UNION 而不是 UNION ALL，那么 SQLite 将不得不保留所有先前生成的内容以检查重复项。因此，程序员应该努力在可行的情况下使用 UNION ALL 而不是 UNION。
以下是前一个示例的变体
WITH RECURSIVE
cnt(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM cnt
LIMIT 1000000
)
SELECT x FROM cnt;
该变体有两个不同之处。initial-select 是“SELECT 1”而不是“VALUES(1)”。但这只是用不同的语法表示完全相同的内容。另一个变化是，递归是通过 LIMIT 而不是 WHERE 子句停止的。使用 LIMIT 表示当一百万行被添加到“cnt”表中（并通过主 SELECT 返回，这得益于查询优化器）时，无论队列中还有多少行，递归都会立即停止。在更复杂的查询中，有时很难确保 WHERE 子句最终会导致队列清空并终止递归。但是 LIMIT 子句总是会停止递归。因此，如果已知递归的大小上限，则最好始终包含一个 LIMIT 子句作为安全措施。
考虑一个表，该表描述了组织成员以及组织内部的指挥链
CREATE TABLE org( name TEXT PRIMARY KEY, boss TEXT REFERENCES org, height INT, -- other content omitted );
组织中的每个成员都有一个姓名，大多数成员都有一个老板。（整个组织的负责人有一个 NULL 的“boss”字段。）“org”表的行形成了一个树。
以下是一个计算爱丽丝组织中所有人的平均身高（包括爱丽丝）的查询
WITH RECURSIVE
works_for_alice(n) AS (
VALUES('Alice')
UNION
SELECT name FROM org, works_for_alice
WHERE org.boss=works_for_alice.n
)
SELECT avg(height) FROM org
WHERE org.name IN works_for_alice;
下一个示例在一个 WITH 子句中使用两个公用表表达式。下表记录了家谱
CREATE TABLE family( name TEXT PRIMARY KEY, mom TEXT REFERENCES family, dad TEXT REFERENCES family, born DATETIME, died DATETIME -- NULL if still alive -- other content );
“family” 表类似于之前的 “org” 表，区别在于现在每个成员有两个父母。我们希望知道 Alice 所有在世的祖先，从最年长的到最年轻的。首先定义了一个普通的通用表表达式 “parent_of”。这个普通的 CTE 是一个视图，可以用来查找任何个人的所有父母。然后在 “ancestor_of_alice” 递归 CTE 中使用这个普通的 CTE。最后在最终查询中使用递归 CTE。
WITH RECURSIVE
parent_of(name, parent) AS
(SELECT name, mom FROM family UNION SELECT name, dad FROM family),
ancestor_of_alice(name) AS
(SELECT parent FROM parent_of WHERE name='Alice'
UNION ALL
SELECT parent FROM parent_of JOIN ancestor_of_alice USING(name))
SELECT family.name FROM ancestor_of_alice, family
WHERE ancestor_of_alice.name=family.name
AND died IS NULL
ORDER BY born;
假设您有一个无向图，其中每个节点由一个整数标识，边由类似这样的表定义
CREATE TABLE edge(aa INT, bb INT); CREATE INDEX edge_aa ON edge(aa); CREATE INDEX edge_bb ON edge(bb);
索引不是必需的，但它们确实可以提高大型图表的性能。要查找与节点 59 相连的所有图节点，请使用类似以下的查询
WITH RECURSIVE nodes(x) AS ( SELECT 59 UNION SELECT aa FROM edge JOIN nodes ON bb=x UNION SELECT bb FROM edge JOIN nodes ON aa=x ) SELECT x FROM nodes;
在这种情况下，initial-select 是简单的查询 “SELECT 59”。这建立了基本情况。该 recursive-select 由另外两个 SELECT 语句组成。第一个递归 SELECT 沿 bb-to-aa 方向跟踪边，第二个递归 SELECT 沿 aa-to-bb 方向跟踪边。使用 UNION 而不是 UNION ALL 是为了防止递归在图包含循环的情况下进入无限循环。
以下是对无向图执行图查询的现实示例：版本控制系统 (VCS) 通常会将项目的演变版本存储为有向无环图 (DAG)。将项目的每个版本称为“签入”。单个签入可以有零个或多个父节点。大多数签入（除了第一个）只有一个父节点，但在合并的情况下，签入可能有两个或三个或更多父节点。用于跟踪签入及其发生顺序的模式可能类似于这样
CREATE TABLE checkin( id INTEGER PRIMARY KEY, mtime INTEGER -- timestamp when this checkin occurred ); CREATE TABLE derivedfrom( xfrom INTEGER NOT NULL REFERENCES checkin, -- parent checkin xto INTEGER NOT NULL REFERENCES checkin, -- derived checkin PRIMARY KEY(xfrom,xto) ); CREATE INDEX derivedfrom_back ON derivedfrom(xto,xfrom);
该图是无环的。我们假设每个子签入的 mtime 都不小于其所有父节点的 mtime。但与之前的示例不同，该图在任何两个签入之间可能存在多个长度不同的路径。
我们想知道签入 “@BASELINE” 的时间上最近的二十个祖先（在整个 DAG 中数以千计的祖先中）。（Fossil VCS 使用类似的查询来显示签入的 N 个最近的祖先。例如：https://www.sqlite.org/src/timeline?p=trunk&n=30。）
WITH RECURSIVE
ancestor(id,mtime) AS (
SELECT id, mtime FROM checkin WHERE id=@BASELINE
UNION
SELECT derivedfrom.xfrom, checkin.mtime
FROM ancestor, derivedfrom, checkin
WHERE ancestor.id=derivedfrom.xto
AND checkin.id=derivedfrom.xfrom
ORDER BY checkin.mtime DESC
LIMIT 20
)
SELECT * FROM checkin JOIN ancestor USING(id);
递归选择中的 “ORDER BY checkin.mtime DESC” 项通过防止查询跟踪来自很久以前的合并签入的分支，从而使查询运行得更快。ORDER BY 强制递归选择专注于最新的签入，即我们想要的签入。如果没有在递归选择上使用 ORDER BY，则必须计算成千上万的祖先的完整集合，对它们全部进行排序，然后取前二十个。ORDER BY 本质上设置了一个优先级队列，强制递归查询首先查看最新的祖先，从而允许使用 LIMIT 子句将查询范围限制在仅我们感兴趣的签入。
递归选择上的 ORDER BY 子句可用于控制对树的搜索是深度优先还是广度优先。为了说明这一点，我们将使用上面示例中 “org” 表的变体，不包含 “height” 列，并插入一些实际数据
CREATE TABLE org(
name TEXT PRIMARY KEY,
boss TEXT REFERENCES org
) WITHOUT ROWID;
INSERT INTO org VALUES('Alice',NULL);
INSERT INTO org VALUES('Bob','Alice');
INSERT INTO org VALUES('Cindy','Alice');
INSERT INTO org VALUES('Dave','Bob');
INSERT INTO org VALUES('Emma','Bob');
INSERT INTO org VALUES('Fred','Cindy');
INSERT INTO org VALUES('Gail','Cindy');
以下查询以广度优先模式显示树结构
WITH RECURSIVE
under_alice(name,level) AS (
VALUES('Alice',0)
UNION ALL
SELECT org.name, under_alice.level+1
FROM org JOIN under_alice ON org.boss=under_alice.name
ORDER BY 2
)
SELECT substr('..........',1,level*3) || name FROM under_alice;
“ORDER BY 2”（与 “ORDER BY under_alice.level+1” 相同）会导致组织图表中较高的级别（具有较小的 “level” 值）首先处理，从而导致广度优先搜索。输出结果为
Alice ...Bob ...Cindy ......Dave ......Emma ......Fred ......Gail
但如果我们将 ORDER BY 子句更改为添加 “DESC” 修饰符，则会导致组织中较低的级别（具有较大的 “level” 值）首先由递归选择处理，从而导致深度优先搜索
WITH RECURSIVE
under_alice(name,level) AS (
VALUES('Alice',0)
UNION ALL
SELECT org.name, under_alice.level+1
FROM org JOIN under_alice ON org.boss=under_alice.name
ORDER BY 2 DESC
)
SELECT substr('..........',1,level*3) || name FROM under_alice;
此修订查询的输出结果为
Alice ...Bob ......Dave ......Emma ...Cindy ......Fred ......Gail
当从递归选择中省略 ORDER BY 子句时，队列的行为类似于 FIFO，这会导致广度优先搜索。
以下查询计算曼德布罗集的近似值，并将结果输出为 ASCII 艺术
WITH RECURSIVE
xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
m(iter, cx, cy, x, y) AS (
SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
UNION ALL
SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m
WHERE (x*x + y*y) < 4.0 AND iter<28
),
m2(iter, cx, cy) AS (
SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
),
a(t) AS (
SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '')
FROM m2 GROUP BY cy
)
SELECT group_concat(rtrim(t),x'0a') FROM a;
在此查询中， “xaxis” 和 “yaxis” CTE 定义了将近似曼德布罗集的点网格。 “m(iter,cx,cy,x,y)” CTE 中的每一行表示，经过 “iter” 次迭代后，从 cx,cy 开始的曼德布罗迭代已经达到点 x,y。此示例中的迭代次数限制为 28（这严重限制了计算的分辨率，但足以用于低分辨率 ASCII 艺术输出）。“m2(iter,cx,cy)” CTE 保存从点 cx,cy 开始时的最大迭代次数。最后，“a(t)” CTE 中的每一行都包含一个字符串，该字符串是输出 ASCII 艺术的一行。最后面的 SELECT 语句只是查询 “a” CTE 以逐行检索所有 ASCII 艺术行。
在 SQLite 命令行 shell 中运行上面的查询会导致以下输出结果
....#
..#*..
..+####+.
.......+####.... +
..##+*##########+.++++
.+.##################+.
.............+###################+.+
..++..#.....*#####################+.
...+#######++#######################.
....+*################################.
#############################################...
....+*################################.
...+#######++#######################.
..++..#.....*#####################+.
.............+###################+.+
.+.##################+.
..##+*##########+.++++
.......+####.... +
..+####+.
..#*..
....#
+.
以下查询解决了一个数独难题。难题的状态由一个 81 个字符的字符串定义，该字符串通过从左到右逐行读取谜题方框中的条目，然后从上到下读取而形成。难题中的空白方块由 “.” 字符表示。因此，输入字符串
53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79
对应于这样的难题
5 3 7 6 1 9 5 9 8 6 8 6 3 4 8 3 1 7 2 6 6 2 8 4 1 9 5 8 7 9
这是解决该难题的查询
WITH RECURSIVE
input(sud) AS (
VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
),
digits(z, lp) AS (
VALUES('1', 1)
UNION ALL SELECT
CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
x(s, ind) AS (
SELECT sud, instr(sud, '.') FROM input
UNION ALL
SELECT
substr(s, 1, ind-1) || z || substr(s, ind+1),
instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
FROM x, digits AS z
WHERE ind>0
AND NOT EXISTS (
SELECT 1
FROM digits AS lp
WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
OR z.z = substr(s, (((ind-1)/3) % 3) * 3
+ ((ind-1)/27) * 27 + lp
+ ((lp-1) / 3) * 6, 1)
)
)
SELECT s FROM x WHERE ind=0;
“input” CTE 定义了输入难题。“digits” CTE 定义了一个表，该表包含 1 到 9 之间的所有数字。“x” CTE 负责解决难题。x(s,ind) 中的条目表示 81 个字符的字符串 “s” 是一个有效的数独难题（它没有冲突），并且第一个未知字符位于位置 “ind”，或者如果所有字符位置都已填满，则 ind==0。因此，目标是计算 “ind” 为 0 的 “x” 条目。
求解器通过向 “x” 递归表添加新条目来工作。给定先前的条目，递归选择尝试用 1 到 9 之间的所有实际有效值来填充单个新位置。复杂的 “NOT EXISTS” 子查询是找出每个候选 “s” 字符串是否是一个有效的数独难题的魔力。
最终答案是通过查找 “ind” 等于 0 的字符串来找到的。如果原始数独问题没有唯一解，则查询将返回所有可能的解。如果原始问题无法解决，则不会返回任何行。在这种情况下，唯一的答案是
534678912672195348198342567859761423426853791713924856961537284287419635345286179
通用表表达式的 “AS MATERIALIZED” 和 “AS NOT MATERIALIZED” 形式是源于 PostgreSQL 的非标准 SQL 语法。在 AS 关键字之后使用 MATERIALIZED 或 NOT MATERIALIZED 可以向查询规划器提供关于如何实现 CTE 的非绑定提示。
如果使用 MATERIALIZED 语句，则 select-stmt 将被物化为一个保存在内存或临时磁盘文件中的临时表。然后，无论何时在后续 SQL 中出现 CTE 表名，都将使用该临时表来代替它。由于 select-stmt 是立即计算的，因此无法应用 查询扁平化 或 下推优化 等优化。这种优化损失是一种功能，而不是错误。开发人员可以使用 MATERIALIZED 关键字作为“优化围栏”，以更紧密地控制 SQLite 查询规划器行为。SQLite 从 PostgreSQL 中借鉴了使用 MATERIALIZED 作为优化围栏的想法。
如果使用 NOT MATERIALIZED 语句，则 select-stmt 将被替换为一个子查询，以代替 CTE 表名的每次出现。然后，扁平化 和 下推 等优化将应用于子查询，就好像子查询是直接使用的。尽管名字如此，NOT MATERIALIZED 语句并不能禁止使用物化。查询规划器仍然可以自由地使用物化来实现子查询，如果它认为这是最佳解决方案。NOT MATERIALIZED 的真正含义更接近于“像任何普通的视图或子查询一样对待”。
如果不存在任何提示，则 SQLite 可以自由地选择它认为最有效的任何实现策略。这是推荐的做法。除非有充分的理由，否则不要在通用表表达式上使用 MATERIALIZED 或 NOT MATERIALIZED 关键字。
MATERIALIZED 和 NOT MATERIALIZED 提示仅在 SQLite 3.35.0（2021-03-12）及更高版本中可用。
WITH 子句不能在 CREATE TRIGGER 中使用。
WITH 子句必须出现在顶级 SELECT 语句的开头，或出现在子查询的开头。WITH 子句不能放在 复合选择 的第二个或后续 SELECT 语句之前。
SQL:1999 规范要求 RECURSIVE 关键字出现在包含递归通用表表达式的任何 WITH 子句的 WITH 之后。但是，为了与 SqlServer 和 Oracle 兼容，SQLite 不强制执行此规则。
此页面上次修改于 2024-01-29 11:00:27 UTC