SQL sqlite3 上的基本递归查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7456957/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
basic recursive query on sqlite3?
提问by Eric
I have a simple sqlite3 table that looks like this:
我有一个简单的 sqlite3 表，如下所示：
Table: Part
Part SuperPart
wk0Z wk00
wk06 wk02
wk07 wk02
eZ01 eZ00
eZ02 eZ00
eZ03 eZ01
eZ04 eZ01
I need to run a recursive query to find all the pairs of a given SuperPart with all of its subParts. So let's say that I have eZ00. eZ00 is a superpart of eZ01 and eZ01 is a superpart of eZ03. The result must include not only the pairs (eZ00, eZ01) and (eZ01 and eZ03) but must also include the pair (eZ00, eZ03).
我需要运行递归查询来查找给定 SuperPart 及其所有子部件的所有对。假设我有 eZ00。eZ00 是 eZ01 的超部件，eZ01 是 eZ03 的超部件。结果不仅必须包括对 (eZ00, eZ01) 和 (eZ01 和 eZ03)，还必须包括对 (eZ00, eZ03)。
I know there are other ways of defining the table, but I have no choice here. I know i can use several unions if I know the depth of my tree, but I won't allways know how depth I want to go. It'd help to have something like WITH RECURSIVE or even just WITH (,,) AS x but for what I've searched, that's not possible in sqlite, right?
我知道还有其他定义表格的方法，但我在这里别无选择。我知道如果我知道我的树的深度，我可以使用多个联合，但我并不总是知道我想要多深。使用 WITH RECURSIVE 或什至只是 WITH (,,) AS x 之类的东西会有所帮助，但是对于我搜索过的内容，这在 sqlite 中是不可能的，对吧？
Is there a way to do this recursive query in sqlite3?
有没有办法在 sqlite3 中执行这个递归查询？
UPDATE:
更新：
When this question was made, SQLite didn't support recursive queries, but as stated by @lunicon, SQLite now supports recursive CTE since 3.8.3 sqlite.org/lang_with.html
提出这个问题时，SQLite 不支持递归查询，但正如@lunicon 所述，SQLite 现在支持递归 CTE，因为 3.8.3 sqlite.org/lang_with.html
回答by mu is too short
If you're lucky enough to be using SQLite 3.8.3 or higherthen you do have access to recursive and non-recursive CTEs using WITH:
如果您有幸使用 SQLite 3.8.3 或更高版本，那么您确实可以使用WITH访问递归和非递归 CTE ：
Thanks to luniconfor letting us know about this SQLite update.
感谢lunicon让我们知道这个 SQLite 更新。
In versions prior to 3.8.3, SQLite didn't support recursive CTEs (or CTEs at all for that matter) so there was no WITH in SQLite. Since you don't know how deep it goes, you can't use the standard JOIN trick to fake the recursive CTE. You have to do it the hard way and implement the recursion in your client code:
在3.8.3 之前的版本中，SQLite 不支持递归 CTE（或根本不支持 CTE），因此SQLite中没有WITH。由于您不知道它的深度，因此您无法使用标准的 JOIN 技巧来伪造递归 CTE。您必须以艰难的方式做到这一点，并在您的客户端代码中实现递归：
- Grab the initial row and the sub-part IDs.
- Grab the rows and sub-part IDs for the sub-parts.
- Repeat until nothing comes back.
- 获取初始行和子部分 ID。
- 获取子部件的行和子部件 ID。
- 重复直到什么都没有回来。
回答by Roman Nazarevych
In this SQLite Release 3.8.3 On 2014-02-03has been added support for CTEs. Here is documentation WITH clauseExample:
在2014 年 2 月 3 日的 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;
回答by Alejadro Xalabarder
Based on the samples found in sqlite with documentation, the query
根据在带有文档的 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
;
would output
会输出
(?, eZ00)
...(eZ00, eZ01)
...(eZ00, eZ02)
......(eZ01, eZ03)
......(eZ01, eZ04)
as "it should be" expected
正如“应该是”预期的那样
the initial record of the recursive table can be replaced with
递归表的初始记录可以替换为
VALUES ((SELECT superpart FROM parts WHERE part='eZ00'), 'eZ00', 0)
in order to get also the parent of the initial superpart, although in this case there is no parent at all.
为了获得初始超部分的父级，尽管在这种情况下根本没有父级。
回答by johndpope
there's a hack http://dje.me/2011/03/26/sqlite-data-trees.html
有一个黑客 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
回答by PirateApp
This is the most basic query that I could think of, it generates a series where we start with 1,2 and keep adding 1 till we hit 20. not much useful but playing around a bit with this will help you build more complex recursive ones
这是我能想到的最基本的查询，它生成一个系列，我们从 1,2 开始，不断加 1，直到我们达到 20。没有多大用处，但稍微玩一下这将有助于您构建更复杂的递归查询
The most basic series
最基本的系列
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
Here is another simple example that generates Fibonacci numbers we start with a = 0, b = 1 and then go a = b, b = a + b just like you would do in any programming language
这是另一个生成斐波那契数的简单示例，我们从 a = 0, b = 1 开始，然后转到 a = b, b = a + b 就像您在任何编程语言中所做的一样
Fibonacci Series
斐波那契数列
WITH b(x,y) AS
(
SELECT 0,1
UNION ALL
SELECT y, x + y
FROM b
WHERE x < 10000
) select * FROM b;
Prints
印刷
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
