[MSSQL] WITH CTE(공통 테이블 식), 재귀 쿼리, 트리구조

MS 문서 : https://learn.microsoft.com/ko-kr/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

WITH common_table_expression(Transact-SQL) - SQL Server

 

마트 혹은 부서의 최상위 카테고리별 하위 카테고리의 개수를 구하는 쿼리

 

use tempdb

-- mart 테이블이 있으면 drop한다.
drop table if exists mart

-- mart 테이블을 생성한다. 
create table mart
(
	category_id		int	primary key 
,	category_name		nvarchar(255)	not null
,	upper_category_id	int	null
foreign key (upper_category_id) references mart(category_id)
)

-- mart 테이블에 데이터를 insert한다.
insert into mart	select	1	,	N'식품'		,	null
	union all	select	2	,	N'주방'		,	null
	union all	select	3	,	N'욕실'		,	null
	union all	select	4	,	N'과일'		,	1
	union all	select	5	,	N'채소'		,	1
	union all	select	6	,	N'푸른채소'	,	4
	union all	select	7	,	N'붉은채소'	,	4
	union all	select	8	,	N'그릇'		,	2
	union all	select	9	,	N'밥그릇'	,	8
	union all	select	10	,	N'접시'		,	8

 

 

mart 테이블을 조회해보면 아래와 같이 출력된다.

---

* 식품
--하위 카테고리 4개
식품 > 채소
식품 > 과일
식품 > 과일 > 푸른채소 
식품 > 과일 > 붉은채소

* 주방
--하위 카테고리 3개
주방 > 그릇
주방 > 그릇 > 밥그릇
주방 > 그릇 > 접시

* 욕실
--하위 카테고리 0개

---

 

가장 하위 카테고리까지 탐색해야하므로 재귀처리를 위해 CTE를 사용하여 조회한다.

;with mart_tree
as
(
	select	category_id = category_id
	,	mycateid = category_id
	,	category_name = category_name
	,	upper_category_id = upper_category_id
	from	mart as a
	union all 
	select	category_id = b.category_id
	,	mycateid = a.category_id
	,	category_name = b.category_name
	,	upper_category_id = b.upper_category_id
	from	mart_tree	as a
	inner join	mart	as b
	on	a.upper_category_id = b.category_id
)
select	category_id = category_id
,	category_name = category_name
,	sum_cnt = sum(case when category_id = mycateid then 0 else 1 end) -- 나 자신이면 0으로 집계
from	mart_tree
where	upper_category_id is null	-- 최상위 카테고리만
group by	category_id, category_name
order by	category_id asc

 

결과

 

최상위 카테고리별로 하위 카테고리가 잘 집계되었다.