리커시브 쿼리

https://mariadb.com/kb/en/recursive-common-table-expressions-overview/

use airbnb;
drop table if exists location;
create table location
(
    id        int primary key auto_increment,
    name      varchar(100),
    type      varchar(30),
    parent_id int references location (id),
    level     int,
    place_id  varchar(60),
    point     point
);
insert into location (id, name, type, parent_id, level, place_id, point)
VALUES (1, '대한민국', 'NATION', null, 1, 'ChIJzWXFYYuifDUR64Pq5LTtioU', POINT(37.59073, 126.97406)),
       (2, '서울특별시', 'CITY', 1, 2, 'ChIJzWXFYYuifDUR64Pq5LTtioU', POINT(37.59073, 126.97406)),
       (3, '송파구', 'DISTRICT', 2, 3, 'ChIJd0dO0JylfDURdcviRsh5HYk', POINT(37.50472, 127.11549)),
       (4, '오금동', 'NEIGHBORHOOD', 3, 4, 'ChIJjeL9n4OvfDUROM87V6iz9mo', POINT(37.50412, 127.13336)),
       (5, '가락1동', 'NEIGHBORHOOD', 3, 4, 'ChIJDe6bBo6lfDURW4', POINT(37.49637, 127.10578)),
       (6, '가락2동', 'NEIGHBORHOOD', 3, 4, 'ChIJ22TW_3uvfDURUJrDWpxx0FI', POINT(37.49890, 127.12640)),
       (7, '서초구', 'DISTRICT', 2, 3, 'ChIJd0dO0JylfDURdcviRsh5HYk', POINT(37.50472, 127.11549)),
       (8, '양재동', 'NEIGHBORHOOD', 7, 4, 'ChIJ22TW_3uvfDURUJrDWpxx0FI', POINT(37.473553, 127.03067)),
       (9, '경기도', 'PROVINCE', 1, 2, 'ChIJ22TW_3uvfDURUJrDWpxx0FI', POINT(37.473553, 127.03067)),
       (10, '연천군', 'COUNTY', 9, 3, 'ChIJ22TW_3uvfDURUJrDWpxx0FI', POINT(37.473553, 127.03067)),
       (11, '미산면', 'TOWNSHIP', 10, 4, 'ChIJ22TW_3uvfDURUJrDWpxx0FI', POINT(37.473553, 127.03067)),
       (12, '삼화리', 'VILLAGE', 11, 5, 'ChIJ22TW_3uvfDURUJrDWpxx0FI', POINT(37.473553, 127.03067)),
       (13, '동이리', 'VILLAGE', 11, 5, 'ChIJ22TW_3uvfDURUJrDWpxx0FI', POINT(37.473553, 127.03067))
;
select name, st_x(`point`), st_y(`point`)
from location;
select *
from location;
select *
from location
where parent_id = ?;
# 서울특별시의 하위 행정을 전부 조회
select *
from location
where parent_id = (select id from location where name = '서울특별시');
# ?의 하위행정을 전부 조회
select *
from location
where parent_id = (select id from location where name = ?);
# ?의 모든 하위행정을 조회(타입무관)
with recursive recursion (id, name, parent_id, type) as (
    select id,
           name,
           parent_id,
           type
    from location
    where parent_id = ?
    union
    select l.id,
           l.name,
           l.parent_id,
           l.type
    from location l
             inner join recursion on l.parent_id = recursion.id
)
select *
from recursion;
# ?의 모든 하위행정을 조회하되, 그 유형이 동이나 리만 검색
with recursive recursion (id, name, parent_id, type) as (
    select id,
           name,
           parent_id,
           type
    from location
    where name = '서울특별시'
    union all
    select l.id,
           l.name,
           l.parent_id,
           l.type
    from location l
             inner join recursion on l.parent_id = recursion.id
)
select *
from recursion
where type = 'NEIGHBORHOOD'
   or type = 'VILLAGE';
# 하위행정 ?의 상위행정을 전부 조회하고, 이를 레벨의 오름차순으로 조회해야한다
with recursive upper (id, name, parent_id, type, level) as (
    select id, name, parent_id, type, level
    from location
    where id = 12
    union
    select l.id, l.name, l.parent_id, l.type, l.level
    from location l,
         upper u
    where l.id = u.parent_id
)
select *
from upper
order by level;