SQL서버 CTE(Common Table Expressions) 사용

조직구조나 BOM 과 같이 계층구조로 된 데이터에 대하여 쉽게 쿼리를 구현할 수 있도록 해주는 것이 CTE,
그중에서도 재귀적 CTE 이다.

create table test1 (mycode varchar(10), uppercode varchar(10)) insert into test1 (mycode, uppercode) select 1, 2 union all select 2, 3 union all select 3, 4 union all select 4, null insert into test1 (mycode, uppercode) select 10, 20 union all select 20, 30 union all select 30, 4 insert into test1 (mycode, uppercode) select 5, 2 go with cteTest(uppercode, mycode, levelno, groupcode) as (        select uppercode, mycode, 0, mycode from test1 where uppercode = 4        union all        select a.uppercode, a.mycode, b.levelno+1, b.groupcode  from test1 a join cteTest b on a.uppercode = b.mycode ) select * from cteTest order by groupcode, levelno go

with cteTest(uppercode, mycode, levelno, groupcode) as (        select uppercode, mycode, 0, mycode from test1 where uppercode = 4        union all        select a.uppercode, a.mycode, b.levelno+1, b.groupcode  from test1 a join cteTest b on a.uppercode = b.mycode ) select        a.groupcode as rootcode,        a.mycode as leafcode from cteTest a where        a.levelno = (select MAX(levelno) from cteTest b                                  where a.groupcode = b.groupcode)

마지막 쿼리는 계층구조의 최하위 레벨과 최상위코드(상위코드가 NULL) + 1인 레벨을 연결한 예이다.
(조직구조의 최하위 조직이 속한 최상위 조직을 알고자 하는 경우)