MSSQL 재귀쿼리 (with) 사용

ERP, MES 관련 회사를 다니면서 BOM관련 문제를 해결했던 쿼리를 정리하고자 올린다.

 

 

네이버 지식백과 관련 정의

BOM(Bill of material)이란 모든 품목에 대해 상위 품목과 부품의 관계와 사용량, 단위 등을 표시한 list, 도표, 또는 그림을 말한다.

 

쉽게 말해서 제조업 회사에서 제품을 완성하기까지 필요한 반제품, 원재료 등을 순차적으로 정의해서 각각의 공정, 재료들의 원가 등을 한눈에 쉽게 보기위해서 만든거라고 한다.

 

내가 받은 업무중에는 최종제품을 0레벨로 하고 제품을 이루는 부품들을 1~3레벨까지 풀어서 한눈에 보기 좋게 데이터를 뿌려주는 쿼리를 만드것이었다.

예) 최종제품 : 컴퓨터 본체(0레벨)  / 렘, CPU 등을 1레벨  /  렘, CPU 등을 만들기 위한 재료들 2레벨 / ----- 3레벨 등

 

 

(필수 컬럼만 가져와서 테이블을 따로 만듬)

dma100 테이블 정보(일반 품목정보)

 

dmb160 테이블 정보(BOM 품목정보)

itm_pid는 현재 품목의 부모의 id를 가르킨다.

itm_cid는 자신을 가르키는 id

 

 

declare @itm_id int
set @itm_id = 2

 
 --재귀쿼리
 ;with BOMCTE as (
 	--처음 1행
 	select itm_pid = 0, itm_cid = a.itm_id, qty = try_cast(0 as decimal(18,5)), lev = 0,
 		   sort_sq = try_cast(right('000' + try_cast(0 as varchar),3) as varchar(100)),
 		   act_qty = try_cast(null as decimal(18,5)), fr_dt, to_dt
 	from dma100 a
 	where a.itm_id = @itm_id
 	
 	union ALL 
 	
 	select b.itm_pid, b.itm_cid, b.usg_qty, lev = a.lev + 1,
 		   sort_sq = try_cast(try_cast(a.sort_sq as varchar(50)) + '/' + right('000' + try_cast(b.sq_no as varchar),3) as varchar(100)),
 		   b.act_qty, b.fr_dt, b.to_dt
 	from BOMCTE a, dmb160 b --BOMCTE ; 누적된 레코드 / B 새로붙여질 대상
 	where a.itm_cid = b.itm_pid
 )
 
 
 select a.sort_sq
 	   ,a.itm_pid
 	   ,a.itm_cid
 	   ,itm_ccd = b.itm_cd
 	   ,itm_cnm = b.itm_nm
 	   ,a.qty
 	   ,case when a.lev = 0 then a.lev end as '0'
 	   ,case when a.lev = 1 then a.lev end as '1'
 	   ,case when a.lev = 2 then a.lev end as '2'
 	   ,case when a.lev = 3 then a.lev end as '3'
 	   ,a.act_qty
 	   ,a.fr_dt
 	   ,a.to_dt
 from BOMCTE a
 left join dma100 b on a.itm_cid = b.itm_id
 order by a.sort_sq, a.lev

 

결과 데이터

 

재귀쿼리를 이용

 

;with BOMCTE as (

 첫번째 select문

(품목정보에서 최종품목을 0레벨)

 

Union all

 

두번째 select문

(BOM정보[dmb160]테이블에서 itm_pid(부모id)를 이용해서 첫번째 select문의 제품을 찾는다)

)