sqlite3 上的基本递归查询? [英] basic recursive query on sqlite3?

问题描述

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

表:部分零件超零件wk0Z wk00wk06 wk02wk07 wk02eZ01 eZ00eZ02 eZ00eZ03 eZ01eZ04 eZ01

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

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

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

更新:

提出这个问题时，SQLite 不支持递归查询，但是 让我们知道这次 SQLite 更新.

<小时>

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

• 获取初始行和子部分 ID.
• 获取子部件的行和子部件 ID.
• 重复直到没有任何反应.

I have a simple sqlite3 table that looks like this:

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).

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?

Is there a way to do this recursive query in 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

解决方案

If you're lucky enough to be using SQLite 3.8.3 or higher then you do have access to recursive and non-recursive CTEs using WITH:

Thanks to lunicon for letting us know about this SQLite update.

---

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:

• 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.

这篇关于sqlite3 上的基本递归查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！