명령문 내에서 임시로 생성한 테이블로 쿼리 실행 중에만 지속되고 파생태이블과 달리 쿼리 실행 중에 여러번 참조 될 수 있다.
기본 구문
WITH cte_name (column_list) AS (
query
)
SELECT * FROM cte_name;
WITH AS 절을 이용하여 ()안의 쿼리문[Select from where]을 이용해 임시 테이블 cte_name을 만들어 이 CTE에 접근이 가능하다.
예시 구문
WITH topsales2003 AS (
SELECT
salesRepEmployeeNumber employeeNumber,
SUM(quantityOrdered * priceEach) sales
FROM
orders
INNER JOIN
orderdetails USING (orderNumber)
INNER JOIN
customers USING (customerNumber)
WHERE
YEAR(shippedDate) = 2003
AND status = 'Shipped'
GROUP BY salesRepEmployeeNumber
ORDER BY sales DESC
LIMIT 5
)
SELECT
employeeNumber,
firstName,
lastName,
sales
FROM
employees
JOIN
topsales2003 USING (employeeNumber);
Mysql 8.0이상에서만 사용할 수 있고, anchor member(재귀 멤버,column)를 재귀 조건에에 따라 반복하며 UNION ALL, UNION DISTINCT와 결합하여 재귀 맴버들을 합친 TABLE을 생성한다.
예시 구문
WITH RECURSIVE cte_name AS (
initial_query -- anchor member
UNION ALL
recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;
재귀 멤버는 집계함수(MAX,MIN,SUM,AVG,COUNT..), GROUP BY, ORDER BY, LIMIT, DISTINCT 를 포함 할 수 없다. (UNION DISTINCT를 사용하는 경우 DISTINCT는 사용가능하다)
사용 예시 구문
WITH RECURSIVE
cte AS (
SELECT 0 AS HOUR
UNION ALL
SELECT HOUR + 1
FROM cte
WHERE HOUR <23)
SELECT
hour, count(o.datetime)
FROM
cte
left JOIN ANIMAL_OUTS AS O
ON cte.hour = HOUR(O.DATETIME)
group by hour;
숫자 0부터 23까지 1씩 증가하는 COLUMN(HOUR)를 가진 TABLE (CTE)를 생성하여 이 cte와 animal_outs라는 테이블과 join한 쿼리문이다.
WITH RECURSIVE employee_paths AS
( SELECT employeeNumber,
reportsTo managerNumber,
officeCode,
1 lvl
FROM employees
WHERE reportsTo IS NULL
UNION ALL
SELECT e.employeeNumber,
e.reportsTo,
e.officeCode,
lvl+1
FROM employees e
INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
managerNumber,
lvl,
city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;
employees테이블에서 reportsTo가 NULL인 row를 시작으로 해당 row의 employeeNumber와 같은 값의 reportsTo를 갖는 row들을 lvl을 증가 시키고 이 row에 대해 또 비교하며 재귀적으로 수행하여 employeeNumber와 같은 reportsTo의 값을 갖는 row가 없을때까지 반복하여 cte를 생성한다.