[본문스크랩] 트리 구조에서 하위 트리 탐색

트리 구조라는 것은 아래 그림과 같은 형태의 데이터 구성을 말한다.

실제 업무에서 이러한 트리 구조 중 가장 많이 접하게 되는 것이 BOM(Bill of Material)이나 조직도 등이 될 것이다.

흔히 BOM 또는 조직도테이블 구성을 1개의 자기 참조형 테이블로 많이 구성하게 된다.

이에 대해 위 방식의 단점을 들어2개의 상호 참조형 테이블로 구성하는 방법에 대해 이화식 선생님의 “대용량 데이터베이스 솔루션” 시리즈를 꼭 한 번 읽어보길 권한다.

어쨌거나 현재 내가 근무하고 있는 회사에서는 조직도를 한 개의 단일 테이블로 구성하고 있다.

테이블은 아래와 같은 구조이다.

idx와 idx_Uplevel은 자기 참조를 위한 Primary Key와 Foriegn Key가 되겠다.

csName 필드는 조직(부서)명, iSeq는 표시 순서가 된다.

최상위 조직, 즉 회사는 idx_Uplevel이 0이다.

회사 클라이언트중 한 곳에서, 부서별 통계 기능 중, 상위 부서를 선택 시, 해당 부서에 속한 모든 하위 부서의 통계를 모두 볼 수 있도록 요청하였다.

이를 위해서는 선택된 부서 아래 속하는 모든 1차적 부서, 그리고 1차적 부서들에 속하는 2차, 이런식으로N차까지 모두 탐색을 해야 했다. 또한 보통 Step 이라 말하는 Depth에는 제약이 없는 상황이다.

위 요구사항을 반영하기 위해 자료를 찾던 중, 거북엄마라는 필명으로 알려진 분의 BOM과 구조탐색에 관한 Article을 하나 발견 했다.

굉장히 좋은 자료이니 꼭 참조해보길 바란다.

위 Article로부터 내가 필요로하는 부분만 재구성해서 Stored Procedure에 아래와 같이 적용하였다.

— @DIRIDX는 외부 어플리케이션으로부터 입력 받은 선택된 부서의 Index 값이다.

SET NOCOUNT ON

DECLARE@LVL INT
DECLARE @TREETBL TABLE
(
idx INT,
lvl INT
)

SET @LVL = 1

INSERT INTO @TREETBL
SELECT idx, @LVL FROM mc_ClassSystem WHERE idx = @DIRIDX

WHILE @@ROWCOUNT > 0
BEGIN
SET @LVL = @LVL + 1

INSERT INTO @TREETBL
SELECT a.idx, @LVL

FROM mc_ClassSystem
AS a JOIN @TREETBL AS b

ON a.idx_Uplevel = b.idx

AND b.lvl = @LVL – 1
END

위와 같이 테이블 변수에 특정 부서 하위에 속하는 모든 부서의 idx 값을 넣은 후 통계 쿼리 내에서 조건절에 아래와 같이 IN 절로 대신하였다.

iSystem_ClassSystem
IN
(
SELECT idx FROM @TREETBL
)

2006-11-16

Oscar sj Park

Geek's Rhapsody