[SQL] SQL WITH RECURSIVE 사용

 

WITH 구문

WITH 구분 이후에 오는 쿼리에서 임시 테이블의 테이블명을 사용하여 값을 참조할 수 있음

WITH TABLE명 AS (
    SELECT - 
)

 

WITH RECURSIVE 문 (재귀 쿼리)

프로그래밍에서 재귀 함수가 있듯이, SQL에서 사용하는 재귀쿼리 기법

WITH RECURSIVE 구분은 가상 테이블을 생성하면서 가상 테이블 자신의 값을 참조하여 재귀적으로 호출하는 방식

WITH [RECURSIVE] TABLE명 AS (
    SELECT -  	# RECURSIVE 사용 시 필수. Non-Recursive 문장 :: 첫번째 루프에서만 실행됨 (초기값 세팅) 
    [UNION ALL] 
    SELECT - 	# Recursive 문장:: 읽어 올 때마다 행의 위치가 기억되어 다음번 읽어 올 때 다음 행으로 이동함
    [WHERE -]   # RECURSIVE 사용 시 필수. 정지 조건 필요할 때 사용
)

• 메모리 상에 가상의 테이블을 저장
• 반드시 UNION을 사용해야 함
• 반드시 Non-Recursive 문장도 최소한 1개 필수 (처음 한번만 실행됨 / 보통 초기값 세팅용도로 사용)
• 서브쿼리에서 가상의 테이블을 참조하는 반복문(문장)이 반드시 필요
• 반복되는 문장은 반드시 정지조건이 요구됨
• 가상의 테이블을 구성하면서 그 자신(가상의 테이블)을 참조하여 값을 결정 할 때 유용함

---

WITH RECURSIVE문 활용

자기참조가 필요한 테이블의 메뉴트리+화면정보를 뿌리기

- 입력: MENU(메뉴정보관리) , SCREEN(화면정보관리)

- 출력: 메뉴트리정보 + 화면정보

MENU(메뉴정보관리)
- menu_id(PK), menu_nm(메뉴명), parent_menu_id(상위메뉴번호), menu_show_seq(동일레벨일경우메뉴표시순서)

 

 

 SCREEN(화면정보관리)
- screen_id(PK), screen_nm(화면명), menu_id(화면포함될메뉴번호), screen_show_seq(동일레벨일경우화면표시순서)

 

최종쿼리 

WITH RECURSIVE wm AS(
    # 최초 1번만 호출쿼리(where조건에 맞는 모든 메뉴번호 한 번에 가져옴. 여기서는 최상위 메뉴)
	select 
		1 no
	    ,rpad(menu_show_seq,5,0) depth -- 메뉴의 depth
        ,menu_id m_id
        ,menu_nm m_nm
        ,menu_show_seq m_show
        from menu 
        where 1=1 
        and parent_menu_id = 0 -- 최상위 ROOT 레벨의 메뉴만을 최초로 가져옴
union all
   # 재귀적으로 호출쿼리
	select 
		wm.no+1  no
         -- 메뉴의 depth :: 최상위 depth(최상위 메뉴 표시순서) + 메뉴 표시 순서
         -- rpad 최종적으로 depth번호대로 정렬을 손쉽게 하기위해 5자리 형식 맞춤 ex)'10000','110000'
		,rpad(concat(replace(wm.depth,0,''),menu_show_seq),5,0) depth
        ,m.menu_id m_id
        ,m.menu_nm m_nm
        ,m.menu_show_seq m_show
        -- 자기참조를 위한 조인
       from menu m right join wm on m.parent_menu_id = wm.m_id
       -- 재귀 호출을 끝낼 임의의 조건(여기선 데이터 6개이므로)
       where m.menu_id <= 6
      
)
# 화면과 아우터 조인으로 메뉴+화면을 출력 (화면이 없는 메뉴정보도 출력을 위해 아우터 조인)
select w.*, s.*
from wm w left outer join screen s
on w.m_id = s.menu_id
order by w.depth, w.m_show
;

 

최종 출력 테이블 (메뉴트리정보 + 화면정보)

 

 

**참고 MENU 테이블 & SCREEN 테이블 생성 쿼리

#MENU
create table MENU as (
select 
  1 menu_id 
,'코딩스터디' menu_nm 
, 0 parent_menu_id  -- root
, 1 menu_show_seq
from dual
union all 
select 
  2 menu_id
, '파이썬' menu_nm
, 3 parent_menu_id
, 2 menu_show_seq 
from dual
union all
select 
3 menu_id 
,'언어' menu_nm
,1 parent_menu_id 
,1 menu_show_seq
from dual
union all
select 
4 menu_id
,'알고리즘'menu_nm
,1 parent_menu_id 
,2 menu_show_seq
from dual
union all
select 
5 menu_id
,'자바' menu_nm
,3 parent_menu_id
,1 menu_show_seq
from dual 
union all
select 
  6 menu_id 
,'재테크스터디' menu_nm 
, 0 parent_menu_id  -- root
, 2 menu_show_seq
from dual
)

#SCREEN
create table SCREEN as (
select 
'scr001' screen_id
,'자바기초' screen_nm
,5 menu_id
,1 screen_show_seq
from dual
union all
select 
'scr002' screen_id
,'파이썬기초' screen_nm
,2 menu_id
,1 screen_show_seq
from dual 
union all
select 
'scr003' screen_id
,'자바중급' screen_nm
,5 menu_id
,2 screen_show_seq
from dual
union all
select 
'scr004' screen_id
,'알고리즘기초' screen_nm
,4 menu_id
,1 screen_show_seq
from dual )

 

 

 

참고 : https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-RECURSIVE-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC

[MYSQL] 📚 RECURSIVE (재귀 쿼리)

https://horang98.tistory.com/10

[MySQL] 계층 쿼리 - WITH, WITH RECURSIVE 사용법