sqlite3上的基本递归查询？

Question

我有一个简单的 sqlite3 表，如下所示：

Table: Part
Part    SuperPart
wk0Z    wk00
wk06    wk02
wk07    wk02
eZ01    eZ00
eZ02    eZ00
eZ03    eZ01
eZ04    eZ01

我需要运行递归查询来查找给定 SuperPart 及其所有子零件的所有对。 假设我有 eZ00。 eZ00 是 eZ01 的超级部分，eZ01 是 eZ03 的超级部分。结果不仅必须包含 (eZ00, eZ01) 和 (eZ01 和 eZ03) 对，而且还必须包含 (eZ00, eZ03) 对。

我知道还有其他定义表的方法，但我在这里别无选择。 我知道如果我知道树的深度，我可以使用多个联合，但我并不总是知道我想要的深度。 拥有类似“WITH RECURSIVE”甚至只是“WITH (,,) AS x”之类的东西会有所帮助，但对于我搜索过的内容，这在 sqlite 中是不可能的，对吧？

有没有办法在sqlite3中进行这种递归查询？

更新：

提出这个问题时，SQLite不支持递归查询，但是正如 @lunicon 所说，SQLite 从 3.8.3 开始支持递归 CTE sqlite.org/lang_with.html

Answer 1

如果您足够幸运使用 SQLite 3.8.3 或更高版本，那么您确实可以使用 WITH：

感谢 lunicon 让我们了解此 SQLite 更新。

---

在 3.8.3 之前的版本中，SQLite 不支持递归 CTE（或根本不支持 CTE），因此没有 在 SQLite 中使用。由于您不知道它有多深，因此您无法使用标准 JOIN 技巧来伪造递归 CTE。您必须以困难的方式做到这一点，并在客户端代码中实现递归：

• 获取初始行和子部分 ID。
• 获取子部件的行和子部件 ID。
• 重复直到没有任何结果。

Answer 2

在此 2014-02-03 发布的 SQLite 3.8.3 中添加了对 CTE 的支持。这是文档 WITH 子句
例子：

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

Answer 3

根据 带有文档的 sqlite 中找到的示例，查询

DROP TABLE IF EXISTS parts;
CREATE TABLE parts (part, superpart);
INSERT INTO parts VALUES("wk0Z", "wk00");
INSERT INTO parts VALUES("wk06", "wk02");
INSERT INTO parts VALUES("wk07", "wk02");
INSERT INTO parts VALUES("eZ01", "eZ00");
INSERT INTO parts VALUES("eZ02", "eZ00");
INSERT INTO parts VALUES("eZ03", "eZ01");
INSERT INTO parts VALUES("eZ04", "eZ01");

WITH RECURSIVE
  under_part(parent,part,level) AS (
     VALUES('?', 'eZ00', 0)
     UNION ALL
     SELECT parts.superpart, parts.part, under_part.level+1 
        FROM parts, under_part
     WHERE parts.superpart=under_part.part
  )
  SELECT SUBSTR('..........',1,level*3) || "(" || parent || ", " || part || ")" FROM under_part
  ;

将输出

  (?, eZ00)
  ...(eZ00, eZ01)
  ...(eZ00, eZ02)
  ......(eZ01, eZ03)
  ......(eZ01, eZ04)

为“应该是”预期的

初始值递归表的记录可以替换为，

VALUES ((SELECT superpart FROM parts WHERE part='eZ00'), 'eZ00', 0)

以便也获得初始超级部分的父级，尽管在这种情况下根本没有父级。

Answer 4

这是我能想到的最基本的查询，它生成一个系列，我们从 1,2 开始，不断添加 1 直到达到 20。没什么用，但稍微玩一下这个将帮助您构建更复杂的递归查询

最基本的数列

WITH b(x,y) AS 
(
    SELECT 1,2 
    UNION ALL 
    SELECT x+ 1, y + 1 
    FROM b 
    WHERE x < 20
) SELECT * FROM b;

Prints

1|2
2|3
3|4
4|5
5|6
6|7
7|8
8|9
9|10
10|11
11|12
12|13
13|14
14|15
15|16
16|17
17|18
18|19
19|20
20|21

这是另一个生成斐波那契数列的简单示例
我们从 a = 0, b = 1 开始，然后 a = b, b = a + b 就像在任何编程语言中所做的那样

斐波那契数列

WITH b(x,y) AS 
(
    SELECT 0,1 
    UNION ALL 
    SELECT y, x + y 
    FROM b 
    WHERE x < 10000
) select * FROM b;

0|1
1|1
1|2
2|3
3|5
5|8
8|13
13|21
21|34
34|55
55|89
89|144
144|233
233|377
377|610
610|987
987|1597
1597|2584
2584|4181
4181|6765
6765|10946
10946|17711

Answer 5

有一个黑客
http://dje.me/2011/03/26/sqlite-data -trees.html

-- A method for storing and retrieving hierarchical data in sqlite3
-- by using a trigger and a temporary table.
-- I needed this but had trouble finding information on it.

-- This is for sqlite3, it mostly won't work on anything else, however 
-- most databases have better ways to do this anyway.

PRAGMA recursive_triggers = TRUE; -- This is not possible before 3.6.18

-- When creating the Node table either use a primary key or some other 
-- identifier which the child node can reference.

CREATE TABLE Node (id INTEGER PRIMARY KEY, parent INTEGER, 
    label VARCHAR(16));

INSERT INTO Node (parent, label) VALUES(NULL, "root");
INSERT INTO Node (parent, label) VALUES(1, "a");
INSERT INTO Node (parent, label) VALUES(2, "b");
INSERT INTO Node (parent, label) VALUES(3, "c1");
INSERT INTO Node (parent, label) VALUES(3, "c2");

-- Create the temp table, note that node is not a primary key
-- which insures the order of the results when Node records are
-- inserted out of order

CREATE TEMP TABLE Path (node INTEGER, parent INTEGER, 
    label VARCHAR(16));

CREATE TRIGGER find_path AFTER INSERT ON Path BEGIN
    INSERT INTO Path SELECT Node.* FROM Node WHERE 
        Node.id = new.parent;
END;


-- The flaw here is that label must be unique, so when creating
-- the table there must be a unique reference for selection
-- This insert sets off the trigger find_path

INSERT INTO Path SELECT * FROM Node WHERE label = "c2";

-- Return the hierarchy in order from "root" to "c2"
SELECT * FROM Path ORDER BY node ASC;

DROP TABLE Path; -- Important if you are staying connected


-- To test this run:
-- sqlite3 -init tree.sql tree.db