Базовый рекурсивный запрос на sqlite3?

У меня есть простая таблица 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 теперь поддерживает рекурсивный CTE, поскольку 3.8.3 sqlite.org/lang_with.html

Ответ 1

Если вам повезло использовать SQLite 3.8.3 или выше, у вас есть доступ к рекурсивным и нерекурсивным CTE с использованием WITH:

Спасибо lunicon за сообщение об обновлении SQLite.

В версиях до 3.8.3 SQLite не поддерживал рекурсивные CTE (или вообще CTE), поэтому в SQLite не было WITH. Поскольку вы не знаете, насколько глубоко это происходит, вы не можете использовать стандартный трюк JOIN, чтобы подделать рекурсивный CTE. Вы должны сделать это трудным путем и реализовать рекурсию в своем клиентском коде:

Захватите начальную строку и идентификаторы подраздела.
Захватите строки и идентификаторы частей для частей.
Повторяйте, пока ничего не вернется.

Ответ 2

В этом SQLite Release 3.8.3 В 2014-02-03 была добавлена поддержка CTE. Вот документация WITH clause Пример:

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

Ответ 3

там взломать 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

Ответ 4

На основе примеров, найденных в 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)

чтобы получить также родителя начальной суперпартии, хотя в этом случае вообще нет родителя.

Ответ 5

Это самый простой запрос, о котором я мог подумать, он генерирует серию, в которой мы начинаем с 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;

Печать

Вот еще один простой пример, который генерирует числа Фибоначчи, мы начинаем с 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