1 방법1

2 방법2

3 실제 적용 예제

4 SQL Server 2005에서는 CTE

5 SQL Server 2008에서는 Hierarchyid

6 참고자료

아래의 예는 계층구조를 코드로 나타내는 것이다. 계층구조를 코드로 나타내는 경우 코드의 정의가 매우 중요하다. 좋은 것은 아래의 예처럼 각각각의 레벨이 1개의 글자로 만들어 지는 것이다. 만약 1개의 문자로 모두 표현이 되지 않는다면 자리 수를 늘리면 되는 것이다. 어떤 레벨은 1글자인데 똑 같은 레벨임에도 불구하고 문자수가 틀리다면 아래와 같이 레벨을 나타내는 컬럼이 만들어져야 한다.

CREATE FUNCTION FN_SUM_AMT (@CODE VARCHAR(10))
RETURNS INT
AS
BEGIN
   DECLARE @SUM INT
   SELECT @SUM = SUM(AMT)
   FROM T_AMT
   WHERE CODE LIKE @CODE + '%'
   RETURN(@SUM)
END
GO
 
SELECT CODE, NAME, TEMPDB.DBO.FN_SUM_AMT(CODE) AS AMT
FROM T_CODE
 
--또는
SELECT  A.DEPT, A.CODE, MIN(A.NAME) NAME, B.YMD, SUM(B.AMT) AMT
FROM    T_CODE  A, T_AMT   B
WHERE   A.DEPT=B.DEPT
AND     B.CODE LIKE A.CODE+'%'
GROUP BY A.DEPT, A.CODE, B.YMD

위에서는 데이터의 연결을 함수를 이용하는 방법과 Join을 통한 연결 액세스를 하였다. 코드로 표현되면 루프를 사용하지 않아도 되는 장점이 있으나 변경에 상당히 취약하다는 약점이 있다. 아래는 웹에 널리 퍼져있는 순환관계의 해법을 필자 나름대로 템플릿을 작성한 것과 그 예제이다.

--순환관계 프로시저
CREATE PROC 프로시저명
AS
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE xtype = 'U' AND name LIKE '%#임시테이블명%')
   DROP TABLE #임시테이블명
 
CREATE TABLE #임시테이블명
(
   컬럼 데이터형 ....
)
 
DECLARE @i AS INT, @path AS VARCHAR(50)
SET @i = 0
SET @path = ','
 
INSERT INTO #임시테이블명
SELECT 컬럼리스트, @i, ',' + 자식컬럼명 + ',' FROM 실제테이블명
WHERE 부모컬럼명 IS NULL --또는 부모컬럼 = 부모컬럼값
 
WHILE @@ROWCOUNT > 0 BEGIN
   SET @i = @i + 1
   INSERT INTO #임시테이블명
   SELECT 실제테이블.컬럼명 .....  , @i, #임시테이블.path + 실제테이블.자식컬럼 + ','
   FROM 실제테이블 INNER JOIN #임시테이블
   ON 실제테이블.부모컬럼 = #임시테이블.자식컬럼
   AND #임시테이블.i = @i - 1
END

SELECT 
	REPLICATE(' ', i) + CAST(i + 1 AS VARCHAR) AS 'LEVEL'
,	REPLICATE('->', i) + 자식컬럼값 AS 'LEVEL_NAME'
,	i
,	path
,	자식컬럼
,	부모컬럼
FROM #임시테이블
ORDER BY path

--DROP TABLE employees
 
CREATE TABLE employees (empid nchar(5) PRIMARY KEY, 
      empname nvarchar(50), 
      mgrid nchar(5) REFERENCES employees(empid)
      )
GO
 
INSERT INTO employees VALUES ('*', '더미', null)
INSERT INTO employees VALUES ('1', '사장', '*')
INSERT INTO employees VALUES ('2', '이사', '1')
INSERT INTO employees VALUES ('3', '이사', '1')
INSERT INTO employees VALUES ('4', '이사', '1')
INSERT INTO employees VALUES ('5', '부장', '2')
INSERT INTO employees VALUES ('6', '부장', '2')
INSERT INTO employees VALUES ('7', '부장', '2')
INSERT INTO employees VALUES ('8', '과장', '3')
INSERT INTO employees VALUES ('9', '과장', '3')
INSERT INTO employees VALUES ('10', '부장', '4')
INSERT INTO employees VALUES ('11', '사원', '7')
INSERT INTO employees VALUES ('12', '사원', '7')
INSERT INTO employees VALUES ('13', '사원', '7')
INSERT INTO employees VALUES ('14', '사원', '7')
INSERT INTO employees VALUES ('111', '사장', '*')
INSERT INTO employees VALUES ('222', '이사', '111')
INSERT INTO employees VALUES ('333', '부장', '222')
INSERT INTO employees VALUES ('444', '과장', '333')
INSERT INTO employees VALUES ('555', '대리', '444')
INSERT INTO employees VALUES ('666', '사원', '555')
SELECT * FROM employees
GO
 
--drop index employees.idx_emp_mgr
create index idx_emp_mgr
on employees(mgrid)
GO
 
ALTER PROC proc_test
AS
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE xtype = 'U' AND name LIKE '%#emp%')
   DROP TABLE #emp
 
CREATE TABLE #emp (empid nchar(5) PRIMARY KEY, 
      empname nvarchar(50), 
      mgrid nchar(5) REFERENCES employees(empid),
      i int,
      path varchar(50)
      )
 
DECLARE @i AS INT, @path AS VARCHAR(50)
SET @i = 0
SET @path = ','
 
INSERT INTO #emp
SELECT empid, empname, mgrid, @i, ',' + empid + ',' FROM employees
WHERE mgrid = '*' --또는 부모컬럼 = 부모컬럼값
 
WHILE @@ROWCOUNT > 0 BEGIN
   SET @i = @i + 1
   INSERT INTO #emp
   SELECT a.empid, a.empname, a.mgrid , @i, b.path + a.empid + ','
   FROM employees a INNER JOIN #emp b
   ON a.mgrid = b.empid
   AND b.i = @i - 1
END
 
SELECT REPLICATE(' ', i) + CAST(i + 1 AS VARCHAR) 'LEVEL', 
             REPLICATE('->', i) + empname 'LEVEL_NAME', 
             i, path, empid, mgrid
FROM #emp
ORDER BY  path
GO
 
exec proc_test

다음은 온라인 도움말에 있는 순환관계 모델의 해법이다. 그리 좋지만은 안은 예제이므로 참고만하기 바란다. Mgrid가 empid를 참조하고 있는 모습이다.

CREATE TABLE employees (empid nchar(5) PRIMARY KEY, 
      empname nvarchar(50), 
      mgrid nchar(5) REFERENCES employees(empid), 
      title nvarchar(30)
      )
GO
 
CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
   empname nvarchar(50) NOT NULL,
   mgrid nchar(5),
   title nvarchar(30))
/*Returns a result set that lists all the employees who report to given 
employee directly or indirectly.*/
AS
BEGIN
   DECLARE @RowsAdded int
   -- table variable to hold accumulated results
   DECLARE @reports TABLE (empid nchar(5) primary key, 
      empname nvarchar(50) NOT NULL,
      mgrid nchar(5),
      title nvarchar(30),
      processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee 
   INSERT @reports
   SELECT empid, empname, mgrid, title, 0
   FROM employees 
   WHERE empid = @InEmpId 
   SET @RowsAdded = @@rowcount
   -- While new employees were added in the previous iteration
   WHILE @RowsAdded > 0
   BEGIN
      /*Mark all employee records whose direct reports are going to be 
   found in this iteration with processed=1.*/
      UPDATE @reports
      SET processed = 1
      WHERE processed = 0
      -- Insert employees who report to employees marked 1.
      INSERT @reports
      SELECT e.empid, e.empname, e.mgrid, e.title, 0
      FROM employees e, @reports r
      WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
      SET @RowsAdded = @@rowcount
      /*Mark all employee records whose direct reports have been found
   in this iteration.*/
      UPDATE @reports
      SET processed = 2
      WHERE processed = 1
   END
   
   -- copy to the result of the function the required columns
   INSERT @retFindReports
   SELECT empid, empname, mgrid, title 
   FROM @reports
   RETURN
END
GO
 
-- Example invocation
SELECT * 
FROM fn_FindReports('11234')
GO

SQL Server 2005버전에서는 순환관계를 해결할 수 있는 방법으로 CTE[1]이란 놈을 선보였다. 자세한 것은 도움말을 참고하기 바란다. 다음은 그 사용법이다.

USE AdventureWorks
GO

WITH T_Recursive(ManagerID, EmployeeID, Title, Level)
AS
(
	--Root.. 
	SELECT ManagerID, EmployeeID, Title, 0 AS Level
	FROM HumanResources.Employee
	WHERE ManagerID IS NULL --Root의 조건

	UNION ALL

	SELECT A.ManagerID, A.EmployeeID, A.Title, B.Level + 1 AS Level
	FROM HumanResources.Employee A INNER JOIN T_Recursive B --재귀쿼리
	ON A.ManagerID = B.EmployeeID
)

SELECT 
	ManagerID
,	CAST(REPLICATE(' --> ', Level) + CAST(EmployeeID AS VARCHAR) AS CHAR(25)) AS EmployeeID
,	Title
,	Level
FROM T_Recursive A
ORDER BY Level

--결과
ManagerID   EmployeeID                Title                                              Level
----------- ------------------------- -------------------------------------------------- -----------
NULL        109                       Chief Executive Officer                            0
109          --> 6                    Marketing Manager                                  1
109          --> 12                   Vice President of Engineering                      1
109          --> 42                   Information Services Manager                       1
109          --> 140                  Chief Financial Officer                            1
109          --> 148                  Vice President of Production                       1
109          --> 273                  Vice President of Sales                            1
273          -->  --> 268             North American Sales Manager                       2
273          -->  --> 284             European Sales Manager                             2
273          -->  --> 288             Pacific Sales Manager                              2
148          -->  --> 21              Production Control Manager                         2
148          -->  --> 44              Master Scheduler                                   2
148          -->  --> 200             Quality Assurance Manager                          2
148          -->  --> 218             Facilities Manager                                 2
140          -->  --> 30              Human Resources Manager                            2
140          -->  --> 71              Finance Manager                                    2
140          -->  --> 103             Assistant to the Chief Financial Officer           2
140          -->  --> 139             Accounts Manager                                   2
.
.
.
274          -->  -->  -->  --> 261   Buyer                                              4
274          -->  -->  -->  --> 264   Purchasing Assistant                               4
274          -->  -->  -->  --> 266   Purchasing Assistant                               4
263          -->  -->  -->  --> 5     Tool Designer                                      4
263          -->  -->  -->  --> 265   Tool Designer                                      4
158          -->  -->  -->  --> 79    Research and Development Engineer                  4
158          -->  -->  -->  --> 114   Research and Development Engineer                  4
158          -->  -->  -->  --> 217   Research and Development Manager                   4

(290 row(s) affected)
(1 row(s) affected)

또 다른 예제

CREATE View [Common].[vDim_Item]
AS
WITH vRecursive(ItemKey, ItemNM, FullPath, OrderPath, ParentKey, ParentNM, Price, ContentsNo, ContentsNM, MenuNo, MenuNM, MixBillNo, VerNo, SiteCD, Level, LeafLevelYN)
AS
(
	SELECT 
		ItemKey
	,	ItemNM
	,	CONVERT(varchar(500), ItemNM) FullPath
	,	CONVERT(varchar(500), ItemKey) OrderPath
	,	ParentKey
	,	CONVERT(varchar(50), NULL) ParentNM
	,	Price
	,	ContentsNo
	,	ContentsNM
	,	MenuNo
	,	MenuNM
	,	MixBillNo
	,	VerNo
	,	SiteCD
	,	0 Level
	,	LeafLevelYN
	FROM Common.Dim_Item
	WHERE ParentKey IS NULL 
	--WHERE ItemKey = 1
	
	UNION ALL

	SELECT 
		A.ItemKey
	,	A.ItemNM
	,	CONVERT(varchar(500), B.FullPath + '/' + A.ItemNM) FullPath
	,	CONVERT(varchar(500), CONVERT(varchar, B.OrderPath) + CONVERT(varchar, A.ItemKey)) OrderPath
	,	A.ParentKey
	,	B.ItemNM ParentNM
	,	A.Price
	,	A.ContentsNo
	,	A.ContentsNM
	,	A.MenuNo
	,	A.MenuNM
	,	A.MixBillNo
	,	A.VerNo
	,	A.SiteCD
	,	B.Level + 1 Level
	,	A.LeafLevelYN
	FROM Common.Dim_Item A INNER JOIN vRecursive B
	ON A.ParentKey = B.ItemKey
	where Level < 10
)
SELECT 
	ParentKey
,	ParentNM
,	ItemKey
,	ISNULL(REPLICATE('│   ', Level-1), '') + CASE WHEN ParentKey IS NOT NULL THEN '└─' ELSE '' END + CAST(ItemKey AS VARCHAR) AS Level_Tree
,	ISNULL(REPLICATE('│   ', Level-1), '') + CASE WHEN ParentKey IS NOT NULL THEN '└─' ELSE '' END + ItemNM ItemNM_Tree
,	ItemNM
,	FullPath
,	OrderPath
,	Price
,	ContentsNo
,	ContentsNM
,	MenuNo
,	MenuNM
,	MixBillNo
,	VerNo
,	SiteCD
,	Level
,	LeafLevelYN
FROM vRecursive A
--OPTION (MAXRECURSION 0);

SQL Server 2008에서는 순환관계 및 계층구조의 해법으로 Hierarchyid 데이터형을 선보였다. Hierarchyid는 다음과 같은 특징을 가진다. 필자는 썩 맘에 들지는 않는다.

• 계층적 작업을 위한 Data type
  
  • 조직구조
    
  • 파일 시스템
    
  • 프로젝트 태스트 집합
    
  • 언어 용어의 분류
    
  • 웹 페이지 간 링크의 그래프
    
• 높은 압축성
  
  

다음은 사용 예제이다.

USE AdventureWorks
GO

--테이블생성
CREATE TABLE HumanResources.EmployeeOrg
(
   OrgNode hierarchyid PRIMARY KEY CLUSTERED, --여기주목!
   OrgLevel AS OrgNode.GetLevel(), --여기주목!
   EmployeeID int UNIQUE NOT NULL,
   EmpName varchar(20) NOT NULL,
   Title varchar(20) NULL
) ;
GO

--인덱스생성(넓이기반인덱스생성)
CREATE UNIQUE INDEX EmployeeOrgNc1 
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode) ;
GO

--루트삽입
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager') ;
GO

--검색1
SELECT * FROM HumanResources.EmployeeOrg

--검색2
SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	OrgNode
,	OrgLevel
,	EmployeeID
,	EmpName
,	Title 
FROM HumanResources.EmployeeOrg
 

* IE���� �ҽ� ����� �ٹٲ� �ȵ�. MS-Word � �ٿ� ��������. �ٹٲ� �Ǵ� ������: Chrome, Opera, Safari

--부하직원 삽입(GetDescendant()메서드사용)

DECLARE @Manager HIERARCHYID 
SELECT @Manager = HIERARCHYID::GetRoot()
FROM HumanResources.EmployeeOrg ;
 
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES
(@Manager.GetDescendant(NULL, NULL), 46, 'Sariya', 'Marketing Specialist') ; 
 
--검색
SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	OrgNode
,	OrgLevel
,	EmployeeID
,	EmpName
,	Title 
FROM HumanResources.EmployeeOrg
 

* IE���� �ҽ� ����� �ٹٲ� �ȵ�. MS-Word � �ٿ� ��������. �ٹٲ� �Ǵ� ������: Chrome, Opera, Safari

--귀찮으니프로시저생성후삽입
CREATE PROC AddEmp(@mgrid int, @empid int, @e_name varchar(20), @title varchar(20)) 
AS 
BEGIN
   DECLARE @mOrgNode hierarchyid, @lc hierarchyid
   SELECT @mOrgNode = OrgNode 
   FROM HumanResources.EmployeeOrg 
   WHERE EmployeeID = @mgrid
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   BEGIN TRANSACTION
      SELECT @lc = max(OrgNode) 
      FROM HumanResources.EmployeeOrg 
      WHERE OrgNode.GetAncestor(1) =@mOrgNode ;

      INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
      VALUES(@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title)
   COMMIT
END ;
GO

EXEC AddEmp 6, 271, 'John', 'Marketing Specialist' ;
EXEC AddEmp 6, 119, 'Jill', 'Marketing Specialist' ;
EXEC AddEmp 46, 269, 'Wanida', 'Marketing Assistant' ;
EXEC AddEmp 271, 272, 'Mary', 'Marketing Assistant' ;
EXEC AddEmp 6, 120, 'yasi', 'Marketing Specialist' ;

--검색
SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	OrgNode
,	OrgLevel
,	EmployeeID
,	EmpName
,	Title 
FROM HumanResources.EmployeeOrg

SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	hierarchyid::GetRoot() AS Text_Root
,	OrgNode.GetAncestor(1) AS Text_Ancestor
,	(SELECT EmpName FROM HumanResources.EmployeeOrg B
	WHERE B.OrgNode = A.OrgNode.GetDescendant(0x78, NULL)) 자식
FROM HumanResources.EmployeeOrg A
WHERE EmpName = 'David'

• Exploring Recursive CTEs by Example
  
• Hierarchies in SQL
  
• Automatic Full Parallel Processing of Hierarchical SQL Queries