[MSSQL] 재귀 CTE를 이용한 계층구조 조회하기

안녕하세요

마푸입니다.

오늘도 "하루 1 포스트"를 달성하기 위해서 늦은시간에 포스트를 작성합니다

데이터베이스를 설계를 하거나 시스템을 분석하다보면 가끔씩 재미있는 상황에 도달하게 됩니다.

바로 테이블 자체의 sequence number가 같은 테이블에서 다른 필드에 참조 되는 경우입니다.

데이터베이스를 학습할때 가장 많이 배우는 테이블 중 하나인 employee 관계에서 쉽게 접할 수 있는 현상인데요

바로 각 점원마다 등급이 있고 각 상급자가 하급자에게 멘티와 멘토 관계에 있는 경우입니다. 멘티와 멘토가 아니더라도 관리자와 사원등의 관계도 해당이 되겠지요

이러한 경우

도출 대상의 결과 테이블과 상급자의 테이블을 조인하기 위해서는 상당히 복잡한 쿼리가 생성이 되는데요

MSSQL에서는 정통적으로 두가지 방법으로 해결할 수 있습니다.

첫번째 방법인 'CTE(Common Table Expression)'를 이용한 '재귀 CTE'로 처리하는 방법입니다. 공용테이블인데요 CTE도 조만간에 정리해서 올리도록 할께요.

CTE는 간단하게 생각하면 드라이브 되는 테이블을 임시의 테이블 명으로 지정해서 사용하는 방법으로 쿼리의 효율성이나 가독성을 매우 높혀주는 기능입니다.

두번째 방법은 MSSQL에서 제공해주는 특별한 방법인데요 바로 'hierarchyid' 구조를 이용해서 작성할 수 있습니다. 이 방법은 복잡한 쿼리가 아니라 데이터의 구조를 작성해서 해당 데이터를 조회하는 방법입니다.

오늘은 그 첫번째 방법인 '재귀 CTE'를 이용한 계층 구조의 조회방법인데요

아래의 쿼리를 이용해서 조회를 진행할 수 있습니다.

--
-- 계층구조
--
 
-- 2008 버전
-- CTE를 이용한 계층구조 구현
use test
GO
 
IF OBJECT_ID ('Employees', 'U') IS NOT NULL
                 DROP TABLE dbo.Employees
 
CREATE TABLE dbo.Employees
(
  EmployeeID int NOT NULL PRIMARY KEY ,
  FirstName varchar(50 ) NOT NULL,
  LastName varchar(50 ) NOT NULL,
  ManagerID int NULL
)
GO
 
INSERT INTO Employees VALUES ( 101, 'Ken', 'Sánchez', NULL)
INSERT INTO Employees VALUES ( 102, 'Terri', 'Duffy', 101 )
INSERT INTO Employees VALUES ( 103, 'Roberto', 'Tamburello', 101 )
INSERT INTO Employees VALUES ( 104, 'Rob', 'Walters', 102 )
INSERT INTO Employees VALUES ( 105, 'Gail', 'Erickson', 102 )
INSERT INTO Employees VALUES ( 106, 'Jossef', 'Goldberg', 103 )
INSERT INTO Employees VALUES ( 107, 'Dylan', 'Miller', 103 )
INSERT INTO Employees VALUES ( 108, 'Diane', 'Margheim', 105 )
INSERT INTO Employees VALUES ( 109, 'Gigi', 'Matthew', 105 )
INSERT INTO Employees VALUES ( 110, 'Michael', 'Raheem', 106 )
go
 
 
WITH
  cteReports (EmpID , FirstName, LastName, MgrID , EmpLevel)
  AS
  (
    SELECT EmployeeID, FirstName, LastName , ManagerID, 1
    FROM Employees
    WHERE ManagerID IS NULL
 
    UNION ALL
 
    SELECT e. EmployeeID, e .FirstName, e.LastName , e. ManagerID, r .EmpLevel + 1
                 FROM Employees e INNER JOIN cteReports r ON e. ManagerID = r .EmpID
  )
SELECT  FirstName + ' ' + LastName AS FullName, EmpLevel ,
  (              SELECT FirstName + ' ' + LastName FROM Employees
    WHERE EmployeeID = cteReports.MgrID ) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID