[SQL] RECURSIVE CTE 구문을 통한 길찾기

0. 목표

 - RECURSIVE CTE 구문을 통한 길찾기

1. 실습

 1) 테이블 생성

CREATE TABLE sql_study.recursive_test
(
	 city_from varchar(10),
     city_to varchar(10)
);

 2) 데이터 삽입

INSERT INTO sql_study.recursive_test(city_from, city_to) VALUES('서울', '대전');
INSERT INTO sql_study.recursive_test(city_from, city_to) VALUES('대전', '대구');
INSERT INTO sql_study.recursive_test(city_from, city_to) VALUES('대구', '부산');

 3) 쿼리 작성

  - UNION ALL 위에서는 베이스 데이터 작성

  - UNION ALL 아래에서 도착지점이 부산이 될 때까지 반복

WITH RECURSIVE a AS (
	SELECT
		city_from,
		city_to,
        CONCAT(city_from, '->', city_to) AS route,
        1 AS route_count
	FROM sql_study.recursive_test
    
    UNION ALL
    
    SELECT
		t.city_from,
		t.city_to,
        CONCAT(a.route, '->', t.city_to) AS route,
        a.route_count + 1 AS route_count
    
    FROM a 
    
    INNER JOIN sql_study.recursive_test AS t
    ON t.city_from = a.city_to
)

SELECT * FROM a;

2. 참고

 - What Is a Recursive CTE in SQL?