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

问题描述

我有一个简单的sqlite3表，看起来像这样:

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

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

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

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

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?

是否可以在sqlite3中执行此递归查询?

Is there a way to do this recursive query in sqlite3?

更新:

提出此问题时，SQLite不支持递归查询，但如所述自@lunicon 以来，SQLite自3.8.3 sqlite.org/lang_with.html 开始支持递归CTE. >

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 3.8.3或更高版本，那么您确实可以使用递归和非递归使用 WITH 的CTE:

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:

感谢 lunicon ，让我们知道此SQLite更新.

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

在版本 3.8.3之前的版本中，SQLite不支持递归CTE(或者完全不支持CTE)，因此没有

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:

• 获取第一行和子部件ID.
• 获取子部件的行和子部件ID.
• 重复直到一切都没有.

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