MSSQL : 계층형 TREE 트리 구조의 TABLE 테이블 구현하기

MSSQL : 계층형 TREE 트리 구조의 TABLE 테이블 구현하기

 

댓글은 PARENT_COMMENT_ID 의 데이터 값이 0 이며, 이를 NULL 로 해도 상관은 없습니다. NULL 사용시 아래 구분의 WHERE 조건에서 IS NULL 로 교체 하시면 되겠습니다. 대댓글(댓글의 댓글)은 상위 댓글의 COMMENT_ID 값을 저장하게 되는 구조입니다.

 

네이버 카페나 다음 카페의 댓글 형태도 트리 형태로 구축되어 있습니다. 즉, 최근에 작성한 글이 하단에 위치한 형태입니다. 

 

계층형 테이블

CREATE TABLE [dbo].[EXER_BBS_COMMENT](
	[COMMENT_ID] [bigint] NOT NULL,
	[PARENT_COMMENT_ID] [bigint] NOT NULL,
	[CONTENT] [nvarchar](4000) NOT NULL,
	[WRITER] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[COMMENT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EXER_BBS_COMMENT] ADD  DEFAULT ((0)) FOR [PARENT_COMMENT_ID]

 

데이터 생성 쿼리

INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (1991314325, 0, N'Zpfzolwfik', N'jjoi****')
GO
INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (1991382505, 1991314325, N'Xtnqnscoaj', N'fami****')
GO
INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (1991391985, 1991314325, N'Nbcqxlhbwn', N'meg_****')
GO
INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (1991392145, 1991314325, N'Usehumqrhc', N'aune****')
GO
INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (1991400075, 1991314325, N'Khxrwadvoy', N'toon****')
GO
INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (1991425415, 1991314325, N'Babnckbgae', N'grea****')
GO
INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (1991458355, 1991314325, N'Rljplzeohl', N'xxx_****')
GO
INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (2014780334, 0, N'Eejyifnsnq', N'jss1****')
GO
INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (2014801434, 2014780334, N'Emfpgnqjoj', N'roji****')
GO
INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (2014804714, 2014780334, N'Corxrfmqqt', N'fina****')
GO
INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (2014808224, 2014780334, N'Lncwayyirt', N'5144****')
GO
INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (2014840284, 2014780334, N'Nzksojhmrr', N'chod****')
GO
INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (2014875304, 2014780334, N'Luxnjbbqwz', N'jogy****')
GO
INSERT [dbo].[EXER_BBS_COMMENT] ([COMMENT_ID], [PARENT_COMMENT_ID], [CONTENT], [WRITER]) VALUES (2014877564, 2014840284, N'Rkfzcobyyi', N'mavi****')
GO

 

저장된 데이터 확인하기

 

계층형 TREE 트리 구조의 TABLE 테이블 호출하기

WITH TREE_QUERY
AS (
	SELECT COMMENT_ID,
        PARENT_COMMENT_ID,
        CONTENT,
        CONVERT(VARCHAR(255), COMMENT_ID)   sort,
        CONVERT(NVARCHAR(255), CONTENT) depth_fullname
    FROM   EXER_BBS_COMMENT
    WHERE  PARENT_COMMENT_ID = 0
    UNION ALL
    SELECT B.COMMENT_ID,
        B.PARENT_COMMENT_ID,
        B.CONTENT,
        CONVERT(VARCHAR(255), CONVERT(NVARCHAR, C.sort) + N' > ' + CONVERT(VARCHAR(255), B.COMMENT_ID))   sort,
        CONVERT(NVARCHAR(255), CONVERT(NVARCHAR, C.depth_fullname) + N' > ' + CONVERT(NVARCHAR(255), B.CONTENT))  depth_fullname
    FROM   EXER_BBS_COMMENT B,
        TREE_QUERY C
    WHERE  B.PARENT_COMMENT_ID = C.COMMENT_ID
)

SELECT COMMENT_ID,
       PARENT_COMMENT_ID,
       CONTENT,
       depth_fullname,
	   sort
FROM   TREE_QUERY
ORDER  BY sort

 

계층형 TREE 트리 구조 결과 보기

 

 

참고 사이트 

 

https://javawork.tistory.com/entry/Mssql-%EC%98%A4%EB%9D%BC%ED%81%B4-%EA%B3%84%EC%B8%B5%ED%98%95-%ED%8A%B8%EB%A6%AC%EA%B5%AC%EC%A1%B0-%EC%BF%BC%EB%A6%AC-%EC%9E%AC%EA%B7%80%ED%98%B8%EC%B6%9C-START-WITH-CONNECT-BY-PRIOR

[Mssql] - 오라클 계층형 트리구조 쿼리 (재귀호출) - START WITH .. CONNECT BY PRIOR

 

https://gimmotti.tistory.com/15

[오라클 계층구조] 게시판 댓글&대댓글 구현