Requête récursive SQL sur la table d'auto-référencement (Oracle)
supposons que j'ai cet échantillon de données:
| Name | ID | PARENT_ID |
-----------------------------
| a1 | 1 | null |
| b2 | 2 | null |
| c3 | 3 | null |
| a1.d4 | 4 | 1 |
| a1.e5 | 5 | 1 |
| a1.d4.f6 | 6 | 4 |
| a1.d4.g7 | 7 | 4 |
| a1.e5.h8 | 8 | 5 |
| a2.i9 | 9 | 2 |
| a2.i9.j10| 10 | 9 |
je voudrais sélectionner tous les enregistrements à partir de accountId = 1, donc le résultat attendu serait:
| Name | ID | PARENT_NAME | PARENT_ID |
-------------------------------------------
| a1 | 1 | null | null |
| a1.d4 | 4 | a1 | 1 |
| a1.e5 | 5 | a1 | 1 |
| a1.d4.f6 | 6 | a1.d4 | 4 |
| a1.d4.g7 | 7 | a1.d4 | 4 |
| a1.e5.h8 | 8 | a1.e5 | 5 |
je suis actuellement capable de faire le select récursif, mais je ne peux pas accéder aux données à partir de la référence parent, donc je ne peux pas retourner parent_name. Le code que j'utilise est (adapté à l'exemple simpliste):
SELECT id, parent_id, name
FROM tbl
START WITH id = 1
CONNECT BY PRIOR id = parent_id
quel SQL devrais-je utiliser pour le mentionné ci-dessus la récupération?
mots clés supplémentaires pour les futurs chercheurs: SQL pour sélectionner des données hiérarchiques représentées par des touches parent dans la même table
5 réponses
Utilisation:
SELECT t1.id,
t1.parent_id,
t1.name,
t2.name AS parent_name,
t2.id AS parent_id
FROM tbl t1
LEFT JOIN tbl t2 ON t2.id = t1.parent_id
START WITH t1.id = 1
CONNECT BY PRIOR t1.id = t1.parent_id
Qu'en est-il de L'utilisation de PRIOR,
donc
SELECT id, parent_id, PRIOR name
FROM tbl
START WITH id = 1
CONNECT BY PRIOR id = parent_id`ou si vous voulez obtenir le nom de racine
SELECT id, parent_id, CONNECT_BY_ROOT name
FROM tbl
START WITH id = 1
CONNECT BY PRIOR id = parent_idutilisant la nouvelle syntaxe de requête imbriquée
with q(name, id, parent_id, parent_name) as (
select
t1.name, t1.id,
null as parent_id, null as parent_name
from t1
where t1.id = 1
union all
select
t1.name, t1.id,
q.id as parent_id, q.name as parent_name
from t1, q
where t1.parent_id = q.id
)
select * from q
vous voulez faire ça?
SELECT id, parent_id, name,
(select Name from tbl where id = t.parent_id) parent_name
FROM tbl t start with id = 1 CONNECT BY PRIOR id = parent_id
Modifier Une autre option basée sur celle D'OMG (mais je pense qu'elle sera également performante):
select
t1.id,
t1.parent_id,
t1.name,
t2.name AS parent_name,
t2.id AS parent_id
from
(select id, parent_id, name
from tbl
start with id = 1
connect by prior id = parent_id) t1
left join
tbl t2 on t2.id = t1.parent_id
C'est un peu lourd, mais je crois que cela devrait fonctionner (sans les joindre). Cela suppose que vous pouvez choisir un caractère qui n'apparaîtra jamais dans le champ en question, pour agir comme un séparateur.
Vous pouvez le faire sans imiter le select, mais je trouve que c'est un peu plus propre que d'avoir quatre références à SYS_CONNECT_BY_PATH.
select id,
parent_id,
case
when lvl <> 1
then substr(name_path,
instr(name_path,'|',1,lvl-1)+1,
instr(name_path,'|',1,lvl)
-instr(name_path,'|',1,lvl-1)-1)
end as name
from (
SELECT id, parent_id, sys_connect_by_path(name,'|') as name_path, level as lvl
FROM tbl
START WITH id = 1
CONNECT BY PRIOR id = parent_id)