requête récursive de base sur sqlite3?
j'ai une table sqlite3 simple qui ressemble à ceci:
Table: Part
Part SuperPart
wk0Z wk00
wk06 wk02
wk07 wk02
eZ01 eZ00
eZ02 eZ00
eZ03 eZ01
eZ04 eZ01
j'ai besoin d'exécuter une requête récursive pour trouver toutes les paires d'un SuperPart donné avec toutes ses sous-parties. Disons que j'ai eZ00. eZ00 est une super partie d'eZ01 et eZ01 est une super partie d'eZ03. Le résultat doit inclure non seulement les paires (eZ00, eZ01) et (eZ01 et eZ03), mais aussi la paire (eZ00, eZ03).
je sais qu'il y a d'autres façons de définir la tableau, mais je n'ai pas le choix ici. Je sais que je peux utiliser plusieurs unions si je connais la profondeur de mon arbre, mais je ne sais pas toujours quelle profondeur je veux aller. Il serait utile d'avoir quelque chose comme avec récursif ou même juste avec (,,) comme x mais pour ce que j'ai cherché, ce n'est pas possible en sqlite, Non?
Est-il un moyen de faire cette requête récursive dans sqlite3?
mise à jour:
quand cette question a été faite, SQLite ne supportait pas les requêtes récursives, mais comme indiqué par @lunicon , SQLite supporte maintenant CTE récursive depuis 3.8.3 sqlite.org/lang_with.html
5 réponses
si vous avez la chance D'utiliser SQLite 3.8.3 ou plus alors vous avez accès à des CTEs récursifs et non-récursifs en utilisant avec :
merci à lunicon pour nous avoir mis au courant de cette mise à jour de SQLite.
dans les versions avant 3.8.3 , SQLite ne supportait pas Ctes récursifs (ou CTEs du tout pour cette matière) donc il n'y avait pas avec en SQLite . Puisque vous ne savez pas à quelle profondeur il va, vous ne pouvez pas utiliser le tour de jointure standard pour simuler le CTE récursif. Vous devez le faire à la dure et implémenter la récursion dans votre code client:
- Saisir la première ligne et de la sous-partie Id.
- Saisir les lignes et sous-partie Id pour les sous-parties.
- répéter jusqu'à rien ne revient.
dans cette version SQLite 3.8.3 sur 2014-02-03 a été ajouté le support pour CTEs. Voici la documentation avec la clause Exemple:
WITH RECURSIVE
cnt(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM cnt
LIMIT 1000000
)
SELECT x FROM cnt;
il y a un piratage 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
basé sur les échantillons trouvés dans sqlite avec documentation , la requête
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
;
sortirait
(?, eZ00)
...(eZ00, eZ01)
...(eZ00, eZ02)
......(eZ01, eZ03)
......(eZ01, eZ04)
comme "il devrait être" prévu
l'enregistrement initial de la table récursive peut être remplacé par
VALUES ((SELECT superpart FROM parts WHERE part='eZ00'), 'eZ00', 0)
afin d'obtenir aussi le parent du superpart initial, bien que dans ce cas il n'y a aucun parent du tout.
c'est la requête la plus basique à laquelle je puisse penser, elle génère une série où nous commençons avec 1,2 et continuons à ajouter 1 jusqu'à ce que nous atteignions 20. pas très utile, mais jouer un peu avec cela vous aidera à construire des plus complexes récursifs
Le plus de la série de base
WITH b(x,y) AS
(
SELECT 1,2
UNION ALL
SELECT x+ 1, y + 1
FROM b
WHERE x < 20
) SELECT * FROM b;
Imprime
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
voici un autre exemple simple qui génère des nombres Fibonacci nous commençons avec a = 0, b = 1 et ensuite aller a = B, b = A + b comme vous le feriez dans n'importe quel langage de programmation
Suite De Fibonacci
WITH b(x,y) AS
(
SELECT 0,1
UNION ALL
SELECT y, x + y
FROM b
WHERE x < 10000
) select * FROM b;
Imprime
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