Zapytanie rekursywne SQL na tablicy SELF referencing (Oracle)

Załóżmy, że mam te przykładowe dane: 

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

Chciałbym wybrać wszystkie rekordy począwszy od accountId = 1, więc oczekiwany wynik będzie: 

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

Obecnie jestem w stanie dokonać rekurencyjnego wyboru, ale wtedy nie mogę uzyskać dostępu do danych z referencji rodzica, dlatego nie mogę zwrócić nazwy parent_name. Kod, którego używam to (dostosowany do prostego przykładu): 

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

Jakiego SQL powinienem użyć do wspomnianego wyżej wyszukiwania?

dodatkowe słowa kluczowe dla przyszłych poszukiwaczy: SQL, aby wybrać hierarchiczne dane reprezentowane przez klucze nadrzędne w tej samej tabeli

Author: Cœur, 2010-02-23
Source

5 answers

Użycie: 

    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
 33
Author: OMG Ponies,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-02-23 16:36:49

A co z używaniem PRIOR,

Więc 

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

Lub jeśli chcesz uzyskać nazwę root 

SELECT id, parent_id, CONNECT_BY_ROOT name
   FROM tbl 
START WITH id = 1 
CONNECT BY PRIOR id = parent_id
 11
Author: ari,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-08-12 09:50:26

Użycie nowej zagnieżdżonej składni zapytania 

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
 10
Author: PaulMurrayCbr,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-07-30 05:45:37

Chcesz to zrobić?

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

Edytuj Inna opcja oparta na OMG ' S one (ale myślę, że sprawdzi się równie dobrze): 

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
 2
Author: Samuel,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-02-23 16:07:29

To trochę uciążliwe, ale uważam, że to powinno zadziałać (bez dodatkowego łączenia). Zakłada się, że można wybrać znak, który nigdy nie pojawi się w danym polu, aby działać jako separator.

Można to zrobić bez zagnieżdżania select, ale uważam to za trochę czystsze, że mając cztery odwołania do 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)
 0
Author: Allan,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-02-25 23:01:33