[mssql] WITH 절 , 비재귀적 CTE

WITH 절은 CTE(Common Table Expression)을 표현하기 위한 구문이다.

CTE는 기존 뷰, 파생테이블, 임시 테이블 들으로 사용되던 것을 대신 할 수 있으며, 더 간결한 식으로 보여지는 장점이 있다.

비재귀적 CTE, 재귀적 CTE 두가지가 있다. 

 

비재귀적 CTE 

단순한 형태이며, 복잡한 쿼리문장을 단순화시키는 데에 적합하게 사용될 수 있다.

 

사용법 

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

WITH 

AAA(열이름)
AS( AAA쿼리문),

BBB(열이름)
AS ( BBB쿼리문),

CCC(열이름)
AS (CCC쿼리문),

SELECT * FROM [AAA OR BBB OR CCC]

 

 

테이블에서 총구매액을 구하여 정렬하고 싶을때

실제 DB를 이용한 예 )   

 

CTLE사용한 예 )

CTE은 조회한 실제 테이블을 ABC라고 정의하고 WITH 문을 만들어 간단히 조회할 수 있다.

SELECT * FROM ABC ORDER BY 총구매액 DESC

 

 

abc는 실존하는 테이블이 아니라, 바로 위에서 with 구문으로 만든 SELECT 결과이다. 

 

 

CTE는 복잡한 쿼리문을 단계별로 분할하여 쿼리를 작성할 때 유용하다 

 

회원테이블에서 각 지역별로 가장 큰 키를 1명씩 뽑은 후, 그 사람들의 키를 평균으로 내야 한다면??

-> 전체 평균이 아니라, 각 지역별로 가장 큰 사람을 구한 후 평균을 구해야 한다면 복잡하다.

이럴때 WITH 구문을 사용해보자. 

1. 각 지역별로 큰 키를 뽑은 후 

2. WITH구문으로 묶고

3. 키의 평균을 구한다.

 

 

CTE의 WITH 절은 CTE 외에 다른 쿼리 문법의 옵션에서도 사용된다.

GO를 써주지 않을 경우 오류가 나기 때문에 

꼭 앞문장과 분리를 위해 GO나 앞문장 뒤에 세미콜론 ; 을 붙여주자 (SELECT * FROM userTb1;)

 

SELECT * FROM userTb1
GO
WITH abc(userid, total)
AS(
    SELECT userid, SUM(price * amount) FROM buyTb1 GROUP BY userid
)
SELECT * FROM abc ORDER BY total 

 

 

 

 

 

 

 

 

 

 

이것이 SQL SERVER다 스터디