|
1. issues 라는 테이블 구조와 데이터는 다음과 같습니다.
select id, subject, parent_id, root_id, lft, rgt from issues;
id subject parent_id root_id lft rgt
68 상위 NULL 68 1 10
379 상위-1 68 68 2 9
380 가_상위-1-1 379 68 3 6
381 나_상위-1-1-1 380 68 4 5
382 다_상위1-2 379 68 7 8
383 가상 NULL 383 1 2
384 하위 NULL 384 1 6
385 하위1-1 384 384 2 3
386 하위1-2 384 384 4 5
INSERT INTO `issues` (`id`, `subject`, `parent_id`, `root_id`, `lft`, `rgt`) VALUES (68, '상위', NULL, 68, 1, 10);
INSERT INTO `issues` (`id`, `subject`, `parent_id`, `root_id`, `lft`, `rgt`) VALUES (379, '상위-1', 68, 68, 2, 9);
INSERT INTO `issues` (`id`, `subject`, `parent_id`, `root_id`, `lft`, `rgt`) VALUES (380, '가_상위-1-1', 379, 68, 3, 6);
INSERT INTO `issues` (`id`, `subject`, `parent_id`, `root_id`, `lft`, `rgt`) VALUES (381, '나_상위-1-1-1', 380, 68, 4, 5);
INSERT INTO `issues` (`id`, `subject`, `parent_id`, `root_id`, `lft`, `rgt`) VALUES (382, '다_상위1-2', 379, 68, 7, 8);
INSERT INTO `issues` (`id`, `subject`, `parent_id`, `root_id`, `lft`, `rgt`) VALUES (383, '가상', NULL, 383, 1, 2);
INSERT INTO `issues` (`id`, `subject`, `parent_id`, `root_id`, `lft`, `rgt`) VALUES (384, '하위', NULL, 384, 1, 6);
INSERT INTO `issues` (`id`, `subject`, `parent_id`, `root_id`, `lft`, `rgt`) VALUES (385, '하위1-1', 384, 384, 2, 3);
INSERT INTO `issues` (`id`, `subject`, `parent_id`, `root_id`, `lft`, `rgt`) VALUES (386, '하위1-2', 384, 384, 4, 5);
parent_id 컬럼에 지정된 값은 부모(parent) id 를 가리킵니다.
예) id 가 379 인 row는 parent_id = 68이므로 id가 68인 row 하위에 속한다.
이 데이터를 기준으로 다음 쿼리로 트리 형태의 데이터를 뽑아보면
WITH recursive cte AS
(
SELECT id, subject parent_subject, subject, parent_id, 1 as ctg_level, convert(id, char(100)) as ctg_path
FROM issues
WHERE parent_id is null
UNION ALL
SELECT a.id, null parent_subject, a.subject subject, a.parent_id, b.ctg_level + 1, concat(b.ctg_path, '-', a.id)
FROM issues AS a
INNER JOIN cte AS b ON a.parent_id = b.id
)
SELECT * FROM cte order by ctg_path ;
이런식으로 조회됩니다.
id parent_subject subject parent_id ctg_level ctg_path
383 가상 가상 NULL 1 383
384 하위 하위 NULL 1 384
385 NULL 하위1-1 384 2 384-385
386 NULL 하위1-2 384 2 384-386
68 상위 상위 NULL 1 68
379 NULL 상위-1 68 2 68-379
380 NULL 가_상위-1-1 379 3 68-379-380
381 NULL 나_상위-1-1-1 380 4 68-379-380-381
382 NULL 다_상위1-2 379 3 68-379-382
제가 산출하고싶은 원하는 결과는 ctg_level 이 1 기준으로 subject는 가나다 순으로 정렬되고,
가나다 순으로 정렬된 ctg_level 1 하위로 2,3,4... 레벨의 데이터가 위치하게끔 하는것 입니다.
원하는 결과 값
id parent_subject subject parent_id ctg_level ctg_path
383 가상 가상 NULL 1 383
68 상위 상위 NULL 1 68
379 NULL 상위-1 68 2 68-379
380 NULL 가_상위-1-1 379 3 68-379-380
381 NULL 나_상위-1-1-1 380 4 68-379-380-381
382 NULL 다_상위1-2 379 3 68-379-382
384 하위 하위 NULL 1 384
385 NULL 하위1-1 384 2 384-385
386 NULL 하위1-2 384 2 384-386
쿼리로서 방법이 있을까요?
감사합니다.
|
