WITH clause

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。

换句话说，递归公用表表达式必须如下所示：

recursive-cte: 隐藏

cte-table-name: 展示

在递归公用表表达式“recursive table”中调用由cte-table-name命名的表。在上面的递归cte气泡图中，递归表必须在递归选择的FROM子句中出现一次，并且不能出现在初始选择或递归选择中的任何其他位置，包括子查询。初始选择可能是复合选择，但可能不包括ORDER BY，LIMIT或OFFSET。递归选择必须是简单的选择，而不是化合物。递归选择允许包含ORDER BY，LIMIT和/或OFFSET。

计算递归表格内容的基本算法如下：

运行初始选择并将结果添加到队列中。

上述基本程序可以通过以下附加规则进行修改：

如果UNION操作符连接初始选择和递归选择，则只有在队列中没有添加相同的行时，才将行添加到队列中。重复行在被添加到队列之前被丢弃，即使重复行已被递归步骤从队列中提取出来。如果运算符是UNION ALL，则初始选择和递归选择生成的所有行都始终添加到队列中，即使它们是重复的也是如此。当确定一个行是否重复时，NULL值彼此相等并且不等于任何其他值。

递归查询示例

以下查询返回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。

这是前一个例子的变体：

WITH RECURSIVE
  cnt(x) AS (
     SELECT 1
     UNION ALL
     SELECT x+1 FROM cnt
      LIMIT 1000000
  )
SELECT x FROM cnt;

这种变化有两点不同。初始选择是“SELECT 1”而不是“VALUES（1）”。但这些只是用来表达完全相同的语法的不同语法。另一个变化是递归由LIMIT而不是WHERE子句停止。使用LIMIT意味着当第一百万行被添加到“cnt”表（并且由主SELECT返回时，由于查询优化器），那么递归会立即停止，而不管可能留在队列中的行数。在更复杂的查询中，有时很难确保WHERE子句最终会导致队列耗尽并且递归终止。但LIMIT子句总是会停止递归。

分层查询示例

考虑描述组织成员以及该组织内的命令链的表格：

CREATE TABLE org(
  name TEXT PRIMARY KEY,
  boss TEXT REFERENCES org,
  height INT,
  -- other content omitted

组织中的每个成员都有一个名字，大多数成员都有一个老板。（整个组织的负责人有一个NULL“boss”字段）。“org”表的行形成一棵树。

这是一个查询，计算Alice的组织中每个人的平均身高，包括Alice：

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

除了现在每个成员有两个父母以外，“家庭”表类似于较早的“org”表。我们想知道Alice的所有祖先，从最古老到最年轻。首先定义普通的公用表表达式“parent_of”。普通的CTE是一种可以用来查找任何个体的所有父母的观点。那个普通的CTE然后被用在“ancestor_of_alice”递归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;

查询Against图表

版本控制系统（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

这个图是非循环的。我们假设每次入住儿童的时间不低于所有父母的时间。但与前面的示例不同，此图可能在任何两个签入之间有多条不同长度的路径。

我们想要知道最近的二十个祖先（在整个DAG的成千上万的祖先中），以便检查“@BASELINE”。（Fossil VCS 使用与此类似的查询来显示N个最近的支票祖先，例如：http : //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强制递归选择专注于最近的checkins，即我们想要的。如果没有递归选择的ORDER BY，就会被迫计算出成千上万个祖先的完整集合，并将它们全部按mtime排序，然后排在前20位。ORDER BY本质上设置了一个优先级队列，它强制递归查询首先查看最近的祖先，从而允许使用LIMIT子句将查询范围限制为感兴趣的签入。

使用ORDER BY控制深度优先与宽度优先搜索树

递归选择中的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”相同）会导致组织结构图中的较高级别（具有较小的“级别”值）被首先处理，从而导致广度优先搜索。输出是：

Alice
...Bob
...Cindy
......Dave
......Emma
......Fred
......Gail

但是，如果我们更改ORDER BY子句以添加“DESC”修饰符，则会导致组织中较低级别（具有较大“级别”值）首先被递归选择处理，从而导致深度优先搜索：

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，这导致了广度优先搜索。

特殊的递归查询示例

以下查询计算Mandelbrot集的近似值并将结果输出为ASCII-art：

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定义Mandelbrot集将近似的点的网格。“m（iter，cx，cy，x，y）”CTE中的每一行意味着在“iter”迭代之后，从cx，cy开始的Mandelbrot迭代已经到达点x，y。在这个例子中迭代次数限制为28（这严重限制了计算的分辨率，但对于低分辨率ASCII输出是足够的）。“m2（iter，cx，cy）”CTE保存从点cx，cy开始达到的最大迭代次数。最后，“a（t）”CTE中的每一行都包含一个字符串，该字符串是输出ASCII-art的一行。最后的SELECT语句只是查询“a”CTE来逐一检索所有ASCII-art行。

在SQLite命令行shell中运行上面的查询会产生以下输出：

                                    ....#
                                   ..#*..
                                 ..+####+.
                            .......+####....   +
                           ..##+*##########+.++++
                          .+.##################+.
              .............+###################+.+
              ..++..#.....*#####################+.
             ...+#######++#######################.
          ....+*################################.
 #############################################...
          ....+*################################.
             ...+#######++#######################.
              ..++..#.....*#####################+.
              .............+###################+.+
                          .+.##################+.
                           ..##+*##########+.++++
                            .......+####....   +
                                 ..+####+.
                                   ..#*..
                                    ....#
                                    +.

这下一个查询解决了一个Sudoku难题。拼图的状态由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”子查询是计算出每个候选人的字符串是否是有效的数独拼图的魔法。

通过查找ind == 0的字符串找到最终答案。如果最初的数独问题没有独特的解决方案，那么查询将返回所有可能的解决方案。如果原始问题无法解决，则不会返回任何行。在这种情况下，唯一的答案是：

534678912672195348198342567859761423426853791713924856961537284287419635345286179

该解决方案在现代工作站上的计算时间少于300毫秒。

限制和警告

WITH子句不能在CREATE TRIGGER中使用。

 SQLite is in the Public Domain.