Database/MS SQL Server2014.05.16 09:04




SQL Server 에서 Tree 구조형 자료의 처리에 대해서 정리 합니다.

1. Tree 구조로 가져오는 기본형
2. 보모의 이름도 같이 가져오는 구조
3. 속성 정보를 사용한 추가 정보 설정 방법
4. 지정된 노드의 모든 부모들을 가져오는 방법
5. 지정된 노드의 최상위 보모를 가져오는 방법

 

USE tempdb;
GO

IF OBJECT_ID('dbo.tree') IS NOT NULL DROP TABLE dbo.tree
GO

CREATE TABLE dbo.tree
(
    ID INT PRIMARY KEY,
    name VARCHAR(32),
    ParentID INT FOREIGN KEY REFERENCES dbo.tree(ID)
);

INSERT dbo.tree 
SELECT 1, 'grandpa', NULL
UNION ALL SELECT 2, 'dad', 1
UNION ALL SELECT 3, 'me', 2
UNION ALL SELECT 4, 'mom', 1
UNION ALL SELECT 5, 'grandma', NULL;
GO

-- Tree 기본형
WITH cte AS
(
    -- anchor:
    SELECT ID, name, ParentID, [level] = 0
    FROM dbo.tree WHERE ParentID IS NULL
    UNION ALL
    -- recursive:
    SELECT t.ID, t.name, t.ParentID, [level] = cte.[level] + 1
    FROM cte INNER JOIN dbo.tree AS t
    ON t.ParentID = cte.ID
)
SELECT ID, name, ParentID, [level] 
FROM cte
ORDER BY [level]
OPTION (MAXRECURSION 32);
GO

-- Tree : 부모 이름도 가져오기...
WITH cte AS
(
    -- anchor:
    SELECT ID, name, ParentID, name parentName, [level] = 0
    FROM dbo.tree WHERE ParentID IS NULL
    UNION ALL
    -- recursive:
    SELECT t.ID, t.name, t.ParentID, cte.name parentName, [level] = cte.[level] + 1
    FROM cte INNER JOIN dbo.tree AS t
    ON t.ParentID = cte.ID
)
SELECT ID, name, ParentID, parentName, [level] 
FROM cte
ORDER BY [level]
OPTION (MAXRECURSION 32);
GO

-- Tree : 추가 정보 설정
WITH cte AS
(
    -- anchor:
    SELECT ID, name, ParentID, [level] = 0
    FROM dbo.tree WHERE ParentID IS NULL
    UNION ALL
    -- recursive:
    SELECT t.ID, t.name, t.ParentID, [level] = cte.[level] + 1
    FROM cte 
	INNER JOIN dbo.tree AS t ON t.ParentID = cte.ID
)
SELECT distinct cteA.ID, cteA.name, cteA.ParentID, cteA.[level], CASE WHEN cteB.ParentID is not null THEN 'Folder' ELSE 'Node' END
FROM cte cteA
LEFT JOIN cte as cteB ON cteA.ID=cteB.ParentID
ORDER BY [level]
OPTION (MAXRECURSION 32)
GO


-- Tree : 지정된 노드에 대한  부모들의 정보를 가져오기
WITH cte AS
(
    -- anchor:
    SELECT ID, name, ParentID,  [level] = 0
    FROM dbo.tree WHERE ID = 3
    UNION ALL
    -- recursive:
    SELECT t.ID, t.name, t.ParentID, [level] = cte.[level] + 1
    FROM cte INNER JOIN dbo.tree AS t
    ON t.ID = cte.ParentID
)
SELECT ID, name, ParentID, [level] 
FROM cte
ORDER BY [level]
OPTION (MAXRECURSION 32);
GO

-- Tree : 지정된 노드에 대한 최상위 부모의 정보를 가져오기
WITH cte AS
(
    -- anchor:
    SELECT ID, name, ParentID,  [level] = 0
    FROM dbo.tree WHERE ID = 3
    UNION ALL
    -- recursive:
    SELECT t.ID, t.name, t.ParentID, [level] = cte.[level] + 1
    FROM cte INNER JOIN dbo.tree AS t
    ON t.ID = cte.ParentID
)
SELECT cteA.ID, cteA.name, cteA.ParentID, cteA.[level] 
FROM cte cteA
WHERE cteA.level = (SELECT MAX(level) from cte)
OPTION (MAXRECURSION 32);
GO




Posted by 금붕어70