데이터베이스 질문과 답변
안녕하세요!
마리아DB 10.5사용 하고 있습니다.
쿼리 결과를 찾을 수가 없어 고수분께 문의 드립니다.
WITH RECURSIVE CTE AS (
SELECT NOTE_SEQ, -- 최상위 레벨을 찾고
REPLY_SEQ,
C_ID,
MBER_ID,
MBER_NM,
SUBJECT,
CONTENTS,
REG_DT,
0 AS LEVEL
FROM TB_NOTICE
WHERE DEL_YN = 'N'
AND C_ID = '202308031600001'
AND NOTE_SEQ = 32
AND REPLY_SEQ = 0
UNION ALL
SELECT A.NOTE_SEQ, -- 하위 레벨 INNER JOIN
A.REPLY_SEQ,
A.C_ID,
A.MBER_ID,
A.MBER_NM,
A.SUBJECT,
A.CONTENTS,
A.REG_DT,
LEVEL + 1 AS LEVEL
FROM TB_NOTICE A
INNER JOIN CTE ON A.REPLY_SEQ = CTE.NOTE_SEQ
)
SELECT NOTE_SEQ,
IFNULL(REPLY_SEQ, 0) AS REPLY_SEQ,
C_ID, MBER_ID, MBER_NM, SUBJECT, CONTENTS, REG_DT,
ROW_NUMBER() OVER (PARTITION BY NOTE_SEQ ORDER BY REPLY_SEQ DESC) AS ROW_NUM,
LEVEL
FROM CTE ;
현재 결과는 첨부파일 처럼 조회 됩니다.
원하는 결과는 note_seq와 reply_seq 기준으로 부모 자식을 찾는 것인데요!!
note_seq reply_seq
32 0
39 32
43 39
44 39
45 39
46 39
47 39
48 39
49 39
50 39
53 32
40 32
41 32
42 32
51 32
by 마농
[2023.08.23 16:49:48]
WITH RECURSIVE cte AS
(
WITH tb_notice AS
(
SELECT 32 note_seq, 0 reply_seq, '202308031600001' c_id, 'cock' mber_id, '민성' mber_nm, 'test' subject, 'test' contents, '2023-08-21 01' reg_dt, 'N' del_yn
UNION ALL SELECT 39, 32, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 02', 'N'
UNION ALL SELECT 40, 32, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 03', 'N'
UNION ALL SELECT 41, 32, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 04', 'N'
UNION ALL SELECT 42, 32, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 05', 'N'
UNION ALL SELECT 43, 39, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 06', 'N'
UNION ALL SELECT 44, 39, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 07', 'N'
UNION ALL SELECT 45, 39, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 08', 'N'
UNION ALL SELECT 46, 39, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 09', 'N'
UNION ALL SELECT 47, 39, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 10', 'N'
UNION ALL SELECT 48, 39, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 11', 'N'
UNION ALL SELECT 49, 39, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 12', 'N'
UNION ALL SELECT 50, 39, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 13', 'N'
UNION ALL SELECT 51, 32, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 14', 'N'
UNION ALL SELECT 53, 32, '202308031600001', 'tester', '테스터', '', 'test', '2023-08-21 15', 'N'
)
SELECT note_seq -- 최상위 레벨을 찾고
, reply_seq
, c_id
, mber_id
, mber_nm
, subject
, contents
, reg_dt
, 0 AS level
, CAST(LPAD(note_seq, 10, 0) AS VARCHAR(200)) sort_order
FROM tb_notice
WHERE del_yn = 'N'
AND c_id = '202308031600001'
AND note_seq = 32
AND reply_seq = 0
UNION ALL
SELECT a.note_seq -- 하위 레벨 INNER JOIN
, a.reply_seq
, a.c_id
, a.mber_id
, a.mber_nm
, a.subject
, a.contents
, a.reg_dt
, level + 1 AS level
, CONCAT(sort_order, LPAD(a.note_seq, 10, 0)) sort_order
FROM tb_notice a
INNER JOIN cte
ON a.reply_seq = cte.note_seq
AND a.del_yn = 'N'
)
SELECT note_seq
, reply_seq
, c_id
, mber_id
, mber_nm
, subject
, contents
, reg_dt
, ROW_NUMBER() OVER(ORDER BY sort_order) AS row_num
, level
, sort_order
FROM cte
;
) 버튼을 클릭하여 작성 하시면 됩니다.