WITH AS (CTE)

CTE

CTE 란 Common Table Expression 이다.

CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있으며 간결하게 보여질 수 있다.

 

CTE는 비재귀적 CTE와 재귀적 CTE 두 가지가 있다.

 

비재귀적 CTE

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

쿼리문에 대한 것을 CTE_테이블로 정의해놓고, SELECT 문에서 CTE_테이블을 활용한다.

 

 

 

 

SELECT userid AS '사용자', SUM(price*amount) AS '총구매액'
  FROM buyTBL 
  GROYP BY userid;

이 쿼리의 조회 결과가 아래와 같고,

 

 

 

총구매액을 내림차순으로 정렬하고 싶을 때, 쿼리 뒤에 ORDER BY 를 붙이는 방법 대신, CTE 방법을 이용하면 다음과 같다.

 

WITH abc(userid , total)
AS
(
    SELECT userid, SUM(price*amount)
    FROM buytbl GROUP BY userid
)
SELECT * FROM abc ORDER BY total desc;

AS () 안에서 조회 한 buytbl 의 userid, SUM(price*amount) 가 각각

abc 테이블의 userid, total 열로 설정이 된다.

 

이때 주의할 점은 WITH ( 1, 2, 3... ) 의 열 개수와 AS ( SELECT 1, 2, 3... ) 의 열 개수가 같아야 한다.

이 쿼리의 조회 결과는 아래와 같다.

buytbl 을 ORDER BY 한 결과와 같다!

 

 

>>여러 개의 CTE 를 사용한 예제

 

WITH cte_userTBL(addr, maxHeight)
AS
(
    SELECT addr, MAX(height)
    FROM usertbl
    GROUP BY addr
),
cte_addr(addr, height)
AS
(
    SELECT addr, height
    FROM usertbl
    GROUP BY addr
)
SELECT AVG(A.maxHeight*1.0) AS '각 지역별 최고키의 평균' , B.addr FROM cte_userTBL A, cte_addr B;