MariaDB, MySQL : Common Table Expression(CTE), WITH RECURSIVE

 

 

 

 

CTE(Common Table Expression)

📌 CTE(Commin Table Expression)

: WITH로 작성된 임시 테이블

하나의 실행 범위 내에서 정의되는 임시 결과 집합
개체로 저장되지 않고 쿼리 지속 시간 동안만 존재한다는 점에서 파생 테이블과 비슷
파생 테이블과 달리 자체 참조가 가능하며 동일 쿼리에서 여러 번 참조될 수 있음

 

 

 

 

 

 

WITH

📌 WITH

해당 쿼리가 실행되기 전에 쿼리에서 참조할 수 있는 임시 테이블을 만드는 것

내가 필요한 컬럼과 데이터를 미리 정의해 놓고 활용할 수 있음

WITH 테이블이름 AS (
    SELECT 0 AS NUM
    UNION ALL
    SELECT 0 FROM 컬럼명
)

 

 

 

 

 

WITH RECURSIVE

📌 WITH RECURSIVE

가상 테이블을 생성하면서 가상 테이블 자신의 값을 참조하여 값을 결정할 때 사용

 

예제) 0~23 까지 1씩 증가하는 컬럼 생성하기

WITH RECURSIVE 테이블명 AS(
    SELECT 0 AS NUM # 초기값 설정
    UNION ALL
    SELECT NUM+1 FROM 테이블명
    WEHRE NUM < 23 # 반복을 멈추는 조건
)

 

 

 

 

예제) 프로그래머스 - 입양 시각 구하기(2)

WITH RECURSIVE T1 AS (
    SELECT 0 AS NUM
    UNION ALL
    SELECT NUM+1 FROM T1
    WHERE NUM < 23),
    T2 AS (
    SELECT HOUR(DATETIME) HOUR, COUNT(DISTINCT ANIMAL_ID) COUNT
        FROM ANIMAL_OUTS
        GROUP BY HOUR
    )

SELECT T1.NUM, IFNULL(T2.COUNT, '0') COUNT
FROM T1
LEFT OUTER JOIN T2
ON T1.NUM = T2.HOUR
ORDER BY T1.NUM ASC

1. 0 ~ 23까지 1씩 증가하는 테이블 T1 설정

2. 각 시간대 별 입양건수 계산하는 테이블 T2 설정

3. 두 테이블 조인, T2의 값이 NULL인 경우 0 표시

4. 정렬기준 적용