WITH 절과 CTE .MySQL

2023.02.06 - [MySQL] - SQL기본.mySQL

SQL기본.mySQL

 

이전글에서 계속

---

WITH절과 CTE 개요

• WITH절은 CTE(Common Table Expression)를 표현하기 위한 구문
• MySQL 8.0 이후부터 사용 가능하게 됨
• CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등을 대신할 수 있으며 간결한 식으로 보여짐
• CTE는 ANSI-SQL99 표준(기존 SQL은 ANSI-SQL92 기준)
• CTE는 비재귀적 CTE와 재귀적 CTE가 있지만 주로 사용되는 것은 비재귀적 CTE

---

비재귀적 CTE

• 단순한 형태, 복잡한 쿼리문장을 단순화 하는데 적합합니다.

WITH CTE_테이블이름(열 이름)
AS
(
	<쿼리문>
)
SELECT 열 이름 FROM CTE_테이블 이름;

• CTE는 뷰와 용도가 비슷하지만 개선된 점이 많습니다.
• 뷰는 계속 존재해서 다른 구문에서도 사용 가능하지만, CTE와 파생 테이블은 구문이 끝나면 소멸 됩니다.
• 중복 CTE도 허용이 됩니다.

-- 단순하게 보이게 만들어주는 것 CTE
SELECT * FROM ABC ORDER BY 총구매액 DESC;

WITH ABC(USERID, TOTAL)
AS
(
	SELECT USERID AS '사용자', SUM(PRICE*AMOUNT) AS '총구매액'
	FROM BUYTBL GROUP BY USERID
)
SELECT * FROM ABC ORDER BY TOTOAL DESC;

-- 회원테이블(USERTBL)에서 각 지역별로 가장 큰 키를 1명씩 뽑은 후, 그 사람들의 키의 평균을 내보자

-- 각 지역 별로 가장 큰 키 출력하기
SELECT ADDR, MAX(HEIGHT) FROM USERTBL GROUP BY ADDR;

-- WITH
WITH CTE_USERTBL(ADDR, MAXHEIGHT)
AS
(
	SELECT ADDR, MAX(HEIGHT) FROM USERTBL GROUP BY ADDR
)
SELECT AVG(MAXHEIGHT * 1.0) FROM CTE_USERTBL;