Основной рекурсивный запрос на SQLite3?
У меня есть простая таблица SQLite3, которая выглядит так:
Table: Part
Part SuperPart
wk0Z wk00
wk06 wk02
wk07 wk02
eZ01 eZ00
eZ02 eZ00
eZ03 eZ01
eZ04 eZ01
Мне нужно запустить рекурсивный запрос, чтобы найти все пары данного суперпарта со всеми его поддателями. Так что скажем, что у меня есть EZ00. EZ00 - суперпарт EZ01, а EZ01 - суперпарт EZ03. Результатом должен включать не только пары (Ez00, Ez01) и (Ez01 и Ez03), но также должны включать пару (Ez00, Ez03).
Я знаю, что есть и другие способы определения стола, но у меня нет выбора здесь. Я знаю, что могу использовать несколько профсоюзов, если я знаю глубину моего дерева, но я не буду всегда знать, как глубину хочу пойти. Это поможет иметь что-то вроде с рекурсивным или даже только с (,) как x, но для того, что я искал, это невозможно в SQLite, верно?
Есть ли способ сделать этот рекурсивный запрос в SQLite3 ?
Обновление:
Когда был сделан этот вопрос, SQLite не поддерживает рекурсивные запросы, но , как указано @LUnicon , SQLite теперь поддерживает рекурсивный CTE с 3,8,3 SQLite. org / lang_with.html
4 ответа
Если вам посчастливилось использовать SQLite 3.8.3 или выше , то у вас есть доступ к рекурсивным и нерекурсивным CTE, используя WITH :
Спасибо lunicon за сообщение об этом обновлении SQLite.
В версиях до 3.8.3 SQLite не поддерживал рекурсивные CTE (или вообще CTE), поэтому в SQLite не было WITH. Поскольку вы не знаете, насколько глубоко это происходит, вы не можете использовать стандартный трюк JOIN, чтобы подделать рекурсивный CTE. Вы должны сделать это сложным способом и реализовать рекурсию в своем клиентском коде:
- Захватить начальную строку и идентификаторы подразделов.
- Захватите строки и идентификаторы частей для частей.
- Повторяйте, пока ничего не вернется.
В этом выпуске SQLite 3.8.3 2014-02-03 была добавлена поддержка CTE. Вот документация С пунктом Пример:
WITH RECURSIVE
cnt(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM cnt
LIMIT 1000000
)
SELECT x FROM cnt;
есть взлом 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
На основании примеров, найденных в 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)
как ожидаемое «116»
]исходная запись рекурсивной таблицы может быть заменена на
VALUES ((SELECT superpart FROM parts WHERE part='eZ00'), 'eZ00', 0)
, чтобы также получить родительский элемент начальной суперпартии, хотя в этом случае родительского элемента вообще нет.