Small. Fast. Reliable.
Choose any three.
Choose any three.
公用表表达式或CTE的作用类似于仅在单个SQL语句期间存在的临时视图。共有两种常见的表表达式：“普通”和“递归”。普通的公共表表达式通过将主查询中的子查询分解出来，有助于使查询更易于理解。递归公用表表达式提供了对树和图进行分层或递归查询的功能，而该功能在SQL语言中是不可用的。
通过在SELECT，INSERT，DELETE或UPDATE语句之前加上WITH子句，可以创建所有通用表表达式（普通和递归）。单个WITH子句可以指定一个或多个公用表表达式，其中一些是普通的，而某些则是递归的。
普通的公用表表达式的工作方式就像是在单个语句期间存在的视图一样。普通的公共表表达式可用于分解子查询并使整个SQL语句更易于阅读和理解。
WITH子句即使包含RECURSIVE关键字，也可以包含普通的公共表表达式。使用RECURSIVE不会强制公用表表达式是递归的。
递归公用表表达式可用于编写遍历树或图的查询。递归公用表表达式具有与普通公用表表达式相同的基本语法，但具有以下附加属性：
换句话说，递归公用表表达式必须类似于以下内容：
在上图中，“初始选择”表示一个或多个非递归SELECT语句，而“递归选择”表示一个或多个非递归SELECT语句。最常见的情况是，只有一个初始选择和一个 递归选择，但每个选择都可以有多个。
在递归公用表表达式中将由cte-table-name命名的表称为“递归表”。在递归CTE上述气泡图中，递归表必须从每个顶级SELECT语句的子句出现在正好一次在递归选择 ，并且必须不其他地方出现无论是在 初始选择或 递归选择，包括子查询。在初始选择可以是化合物中选择，但它可以不包括ORDER BY，LIMIT，或OFFSET。该递归选择也可以是化合物选择限制是该化合物的所有元素都必须由相同的UNION或UNION ALL运算符分隔，该运算符将initial-select与recursive-select分开 。该递归选择被允许包括ORDER BY，限制和/或偏移，但可以不使用 聚集函数或窗函数。
在版本3.34.0（2020-12-01）中添加了将递归选择作为化合物的功能。在早期版本的SQLite中，递归选择只能是单个简单的SELECT语句。
计算递归表内容的基本算法如下：
上面的基本过程可以通过以下附加规则进行修改：
如果UNION运算符将“初始选择”与“递归选择”连接在一起 ，则仅当先前未将相同的行添加到队列时才将行添加到队列。即使重复行已经从队列中提取出重复的行，重复行也将被丢弃，然后再添加到队列中。如果运算符为UNION ALL，则初始选择和 递归选择生成的所有行即使重复，也总是添加到队列中。在确定是否重复一行时，NULL值相互比较，但不等于任何其他值。
LIMIT子句（如果存在）确定在步骤2b中将被添加到递归表的最大行数。一旦达到限制，递归就会停止。限制为零表示没有行添加到递归表中，而负限制表示无限制的行数可以添加到递归表中。
OFFSET子句（如果存在）并且具有正值N，则可以防止将前N行添加到递归表中。前N行仍由递归选择处理-只是不将它们添加到递归表中。在跳过所有OFFSET行之前，不将行计为达到LIMIT。
如果存在ORDER BY子句，则在步骤2a中确定从队列中提取行的顺序。如果没有ORDER BY子句，则行的提取顺序不确定。（在当前实现中，如果省略ORDER BY子句，则队列将成为FIFO，但应用程序不应依赖于该事实，因为它可能会更改。）
以下查询返回1到1000000之间的所有整数：
有递归 cnt（x）AS（VALUES（1）从cnt的x <1000000处选择所有联结x + 1） 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。
这是前一个示例的变体：
有递归
cnt（x）AS（
选择1
全联盟
从cnt选择x + 1
限额1000000
）
SELECT x FROM cnt;
此变化有两个区别。初始选择是“ SELECT 1”，而不是“ VALUES（1）”。但是，这些只是用于表达完全相同的内容的不同语法。另一个变化是递归由LIMIT而不是WHERE子句停止。LIMIT的使用意味着，当将百万行添加到“ cnt”表中（并由主SELECT返回，这要归功于查询优化器）之后，无论队列中剩余多少行，递归都会立即停止。在更复杂的查询上，有时可能难以确保WHERE子句最终将导致队列耗尽并且递归终止。但是LIMIT子句将始终停止递归。
考虑一个表，该表描述组织的成员以及该组织内的命令链：
创建表org（ 名称TEXT PRIMARY KEY， 老板TEXT REFERENCES org， 高度INT， -其他内容省略 ）;
组织中的每个成员都有一个名字，大多数成员只有一个老板。（整个组织的头都有一个空的“老板”字段。）“组织”表的行形成一棵树。
这是一个查询，它计算出爱丽丝组织中每个人（包括爱丽丝）的平均身高：
有递归
works_for_alice（n）AS（
VALUES（'爱丽丝'）
联盟
从组织中选择名称，works_for_alice
在哪里org.boss = works_for_alice.n
）
从组织中选择avg（height）
哪里org.name在works_for_alice;
下一个示例在单个WITH子句中使用两个公用表表达式。下表记录了家谱：
创建表家族（ 名称TEXT PRIMARY KEY， 妈妈文字参考家庭， 爸爸TEXT REFERENCES家人， 出生于DATETIME， 在DATETIME死亡，-如果还活着则为NULL -其他内容 ）;
“家庭”表与早期的“组织”表相似，不同之处在于现在每个成员都有两个父母。我们想知道爱丽丝的所有祖先，从最老到最年轻。首先定义一个普通的公用表表达式“ parent_of”。普通的CTE是可以用来查找任何个人的所有父母的视图。然后，在“ ancestor_of_alice”递归CTE中使用该普通CTE。然后在最终查询中使用递归CTE：
有递归
parent_of（名称，父级）AS
（选择名称，妈妈来自家庭UNION SELECT名称，爸爸来自家庭），
ancestor_of_alice（name）AS
（从FHERE名称=“爱丽丝”的parent_of中选择父对象
全联盟
SELECT parent FROM parent_of JOIN祖先_alice USING（name））
SELECT family.name FROM ancestor_of_alice，家庭
在哪里ancestor_of_alice.name = family.name
AND死亡为NULL
ORDER BY出生；
假设您有一个无向图，其中每个节点都由一个整数标识，并且边由一个表定义，如下所示：
CREATE TABLE edge（aa INT，bb INT）; CREATE INDEX edge_aa ON edge（aa）; 创建索引edge_bb ON edge（bb）;
索引不是必需的，但它们确实有助于大型图的性能。要查找连接到节点59的图的所有节点，请使用类似于以下内容的查询：
使用递归节点（x）AS（ 选择59 联盟 从bb = x上的边联接节点选择aa 联盟 从aa = x上的边联接节点中选择bb ） SELECT x FROM节点；
在这种情况下 ，初始选择是简单查询“ SELECT 59”。这建立了基本情况。该 递归选择由其他两个SELECT语句的。第一递归SELECT遵循bb-to-aa方向上的边缘，第二递归SELECT遵循aa-bb方向上的边缘。如果图形包含循环，则使用UNION代替UNION ALL来防止递归进入无限循环。
这是对有向图使用图查询的真实示例：版本控制系统（VCS）通常会将项目的演进版本存储为有向无环图（DAG）。将项目的每个版本称为“签到”。一次签到可以有零个或多个父母。大多数签入（第一个签到除外）都具有单亲，但是在合并的情况下，签到可能有两个或三个或更多亲。跟踪签入及其发生顺序的模式可能类似于以下内容：
CREATE TABLE checkin（ id整数主键， mtime INTEGER-签到发生时的时间戳 ）; 创建表派生自（ xfrom INTEGER NOT NULL REFERENCES签入，-父签入 xto INTEGER NOT NULL REFERENCES签入，-派生签入 主键（xfrom，xto） ）; 创建索引派生自_返回派生自（xto，xfrom）;
该图是非循环的。并且我们假定每个孩子签到的mtime不小于其所有父母的mtime。但是与前面的示例不同，此图可能在任意两个签入之间具有长度不同的多个路径。
我们想及时了解二十个最近的祖先（在整个DAG中成千上万的祖先中），以进行“ @BASELINE”签入。（Fossil VCS使用与此类似的查询来显示N个最近签入的祖先。例如：http ://www.sqlite.org/src/timeline?p=trunk&n =30。）
有递归
祖先（id，mtime）AS（
SELECT ID，mtime FROM checkin WHERE id = @ BASELINE
联盟
选择派生自.xfrom，checkin.mtime
来自祖先，派生自签入
在哪里ancestor.id = derivedfrom.xto
AND checkin.id = derivedfrom.xfrom
ORDER BY checkin.mtime DESC
限制20
）
SELECT * FROM checkin加入祖先USING（id）;
递归选择中的“ ORDER BY checkin.mtime DESC”一词通过阻止查询遵循很久以前合并了检入的分支，使查询的运行速度大大提高。ORDER BY强制递归选择集中在最新的签入（我们想要的签入）上。在递归选择中没有ORDER BY的情况下，将被迫计算成千上万个祖先的完整集合，按mtime对它们进行排序，然后取前20名。ORDER BY本质上设置了一个优先级队列，该队列强制递归查询首先查看最近的祖先，从而允许使用LIMIT子句将查询范围限制为仅关注的签入对象。
递归选择上的ORDER BY子句可用于控制树的搜索是深度优先还是宽度优先。为了说明这一点，我们将使用上面示例中的“ org”表的变体，其中没有“ height”列，并插入了一些实际数据：
创建表org（ 名称TEXT PRIMARY KEY， 老板文本参考org ）没有行列； 插入组织值（'Alice'，NULL）; 插入组织值（'Bob'，'Alice'）; 插入组织值（'Cindy'，'Alice'）; 插入组织值（'Dave'，'Bob'）; 插入组织值（'Emma'，'Bob'）; 插入组织值（'Fred'，'Cindy'）; 插入组织值（'Gail'，'Cindy'）;
这是一个以广度优先模式显示树结构的查询：
有递归
under_alice（名称，级别）AS（
VALUES（'爱丽丝'，0）
全联盟
SELECT org.name，under_alice.level + 1
FROM org JOIN under_alice ON org.boss = under_alice.name
按2排序
）
SELECT substr（'..........'，1，level * 3）|| 名字FROM under_alice;
“ ORDER BY 2”（其含义与“ ORDER BY under_alice.level + 1”相同）将导致首先处理组织结构图中较高的级别（具有较小的“级别”值），从而导致广度优先的搜索。输出为：
爱丽丝 ...鲍勃 ...辛迪 ......戴夫 ......艾玛 ......弗雷德 ......盖尔
但是，如果我们更改ORDER BY子句以添加“ DESC”修饰符，则将导致递归选择首先处理组织中的较低级别（具有较大的“级别”值），从而导致深度优先搜索：
有递归
under_alice（名称，级别）AS（
VALUES（'爱丽丝'，0）
全联盟
SELECT org.name，under_alice.level + 1
FROM org JOIN under_alice ON org.boss = under_alice.name
按2 DESC排序
）
SELECT substr（'..........'，1，level * 3）|| 名字FROM under_alice;
此修改后的查询的输出为：
爱丽丝 ...鲍勃 ......戴夫 ......艾玛 ...辛迪 ......弗雷德 ......盖尔
当从递归选择中省略ORDER BY子句时，队列的行为就像一个FIFO，这导致了广度优先的搜索。
以下查询计算Mandelbrot集的近似值并将结果输出为ASCII-art：
有递归
xaxis（x）AS（VALUES（-2.0）UNION ALL SELECT x + 0.05 from xaxis WHERE x <1.2），
yaxis（y）AS（从yaxis y <1.0的VALUES（-1.0）UNION ALL SELECT y + 0.1），
m（iter，cx，cy，x，y）AS（
SELECT 0，x，y，0.0，0.0 FROM xaxis，yaxis
全联盟
从m中选择iter + 1，cx，cy，x * xy * y + cx，2.0 * x * y + cy
在（x * x + y * y）<4.0并且iter <28
），
m2（iter，cx，cy）AS（
从m GROUP BY cx，cy中选择max（iter），cx，cy
），
a（t）AS（
选择group_concat（substr（'。+ *＃'，1 + min（iter / 7,4），1），''）
从m2 GROUP BY CY
）
从a中选择group_concat（rtrim（t），x'0a'）;
在此查询中，“ xaxis”和“ yaxis” CTE定义了将要为其近似Mandelbrot集的点的网格。“ m（iter，cx，cy，x，y）” CTE中的每一行表示在“ iter”迭代之后，从cx，cy开始的Mandelbrot迭代已到达点x，y。在此示例中，迭代次数限制为28（这严重限制了计算的分辨率，但对于低分辨率ASCII艺术输出就足够了）。当从点cx，cy开始时，“ m2（iter，cx，cy）” CTE保持已达到的最大迭代次数。最后，“ 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
这是解决难题的查询：
有递归
输入（sud）AS（
值（'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）
联合所有选择
CAST（lp + 1 AS TEXT），lp + 1从数字中lp <9
），
x（s，ind）AS（
从输入中选择sud，instr（sud，'。'）
全联盟
选择
substr（s，1，ind-1）|| z || substr（s，ind + 1），
instr（substr（s，1，ind-1）|| z || substr（s，ind + 1），'。'）
FROM x，数字为AS z
在哪里ind> 0
并且不存在（
选择1
FROM digits AS lp
其中zz = substr（s，（（ind-1）/ 9）* 9 + lp，1）
或zz = substr（s，（（ind-1）％9）+（lp-1）* 9 +1，1）
或zz = substr（s，（（（（ind-1）/ 3）％3）* 3
+（（ind-1）/ 27）* 27 + lp
+（（lp-1）/ 3）* 6，1）
）
）
从x WHERE ind = 0处选择s；
“输入” CTE定义输入难题。“数字” 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是立即评估的，因此失去了应用优化（如查询展平或下推式优化）的机会 。（然后，CTE充当“优化围栏”。）
如果使用NOT MATERIALIZED短语，那么select-stmt 会替换为每次出现的CTE表名称的子查询。优化，例如平坦化和 下推，然后施加到子查询，如同子查询具有由在直接使用。尽管名称如此，但NOT MATERIALIZED短语并不禁止使用实现。如果认为这是最好的解决方案，查询计划者仍然可以使用实现来自由地实现子查询。NOT MATERIALIZED的真正含义更接近于“像任何普通视图或子查询一样进行处理”。
如果两个提示均不存在，则SQLite 3.35.0（2021-03-12）和更高版本将处理CTE，如果多次使用CTE则好像存在MATERIALIZED短语，或者如果未使用CTE则出现NOT NOTTERIALIZED短语。 CTE仅使用一次。在SQLite 3.35.0之前，所有CTE都被视为好像存在NOT MATERIALIZED短语。如果我们提出了更好的查询计划策略，则根据使用次数来决定是否将特定的CTE视为已材料化或未材料化是一种试探法，此启发法可能会在将来的发行版中进行更改。区别纯粹是性能问题。等效答案应以任何一种方式计算。
应用程序不应该依赖于此提示在何时或频繁调用用户定义的函数方面的语义影响。查询计划程序的未来版本可能会忽略此提示。
MATERIALIZED和NOT MATERIALIZED提示仅在SQLite版本3.35.0（2021-03-12）和更高版本中可用。
WITH子句不能在CREATE TRIGGER中使用。
WITH子句必须出现在顶级SELECT语句的开头或子查询的开头。WITH子句不能放在复合select的第二个或后续SELECT语句之前。
SQL：1999规范要求RECURSIVE关键字在任何包含递归公用表表达式的WITH子句中都紧跟WITH。但是，为了与SqlServer和Oracle兼容，SQLite不强制执行此规则。