tablas - ejemplos de consultas en oracle sql developer

Consulta recursiva de SQL en la tabla de autorreferencia(Oracle)

Question

Supongamos que tengo esta muestra de datos:

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

Me gustaría seleccionar todos los registros desde accountId = 1, por lo que el resultado esperado sería:

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

Actualmente puedo seleccionar recursivo, pero luego no puedo acceder a los datos de la referencia principal, por lo tanto, no puedo devolver parent_name. El código que estoy usando es (adaptado al ejemplo simplista):

SELECT id, parent_id, name
FROM tbl
START WITH id = 1
CONNECT BY PRIOR id = parent_id

¿Qué SQL debería utilizar para la recuperación mencionada anteriormente?

Palabras clave adicionales para buscadores futuros: SQL para seleccionar datos jerárquicos representados por claves principales en la misma tabla

Answer 1

¿Qué pasa con el uso de PRIOR,

asi que

SELECT id, parent_id, PRIOR name
FROM tbl
START WITH id = 1
CONNECT BY PRIOR id = parent_id`

o si quieres obtener el nombre de raíz

SELECT id, parent_id, CONNECT_BY_ROOT name
FROM tbl
START WITH id = 1
CONNECT BY PRIOR id = parent_id

Answer 2

¿Quieres hacer esto?

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

Editar Otra opción basada en OMG (pero creo que tendrá el mismo rendimiento):

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

Answer 3

Es un poco incómodo, pero creo que esto debería funcionar (sin la unión extra). Esto supone que puede elegir un personaje que nunca aparecerá en el campo en cuestión, para actuar como un separador.

Puede hacerlo sin anidar la selección, pero me parece un poco más limpio que tener cuatro referencias a 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)

Answer 4

Usar la nueva sintaxis de consulta anidada

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

Answer 5

Utilizar:

 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