CTE (common_table_expression)

catecode	depth	catename
111	1	1카테고리
111111	2	2카테고리
111111111	3	3카테고리

위 처럼 카테고리 구조가 되어있는 경우 전체 카테고리를 가져오려면

난감한 경우가 생긴다.

SELECT c1.catecode, c1.catename, c2.catecode, c2.catename, c3.catecode, c3.catename, c4.catecode, c4.catename

FROM categoryTBL as c1

LEFT JOIN categoryTBL  as c2 on c1.catecode = LEFT(c2.catecode, 3)

LEFT JOIN categoryTBL  as c3 on c2.catecode = LEFT(c3.catecode, 6)

LEFT JOIN categoryTBL  as c4 on c3.catecode = LEFT(c4.catecode, 9) 

WHERE c1.depth =1

ORDER BY LEFT(c1.catecode, 3) asc

위 처럼 LEFT JOIN을 여러개써서 구현가능 할 수도 있으나, 만약 뎁스가 100만개라면?

100만번을 LEFT JOIN을 걸어줘야 하는 노가다가 생긴다.

이럴 때 쓰는 것이 with CTE 이다.

drop table #CleanTable

SELECT catecode

, case when (len(catecode) > 3) THEN LEFT(catecode, len(catecode) -3) else '0' end as parentCatecode

, catename

INTO #CleanTable

FROM categoryTBL 

;WITH SAMPLE_CTE(catecode, parentCatecode, catename, catename2, LV) as (

SELECT A.catecode, A.parentCatecode

, convert(varchar(300), A.catename) as catename

, catename as catename2

, 1

FROM #CleanTable A

WHERE A.parentCatecode = '0'

UNION ALL

SELECT B.catecode, B.parentCatecode

, convert(varchar(300), C.catename + ' > ' + B.cateName) as catename

, B.catename as catename2

, (C.LV + 1) LV

FROM #CleanTable B,

SAMPLE_CTE C

WHERE B.parentCatecode = C.catecode

)

SELECT catecode, parentCatecode, catename, catename2, LV

FROM SAMPLE_CTE

ORDER BY catename, LV

위처럼 간단하게(?) 구현 가능하다.

참고 : https://docs.microsoft.com/ko-kr/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017