database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Q&A 31330 게시물 읽기
No. 31330
재귀호출에 의한 하이락키 구조의 트리형태의 정렬
작성자
김씨
작성일
2021-01-22 10:28
조회수
3,639

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


쿼리로서 방법이 있을까요? 

감사합니다.




 

이 글에 대한 댓글이 총 4건 있습니다.

정렬 기준을 바꾸시면 되죠.

- 변경전 : ctg_path 를 만들 때 id 를 연결

- 변경후 : ctg_path 를 만들 때 subject 를 연결

마농(manon94)님이 2021-01-25 09:17에 작성한 댓글입니다.

감사합니다.

제가 너무 어렵게 생각했네요

매수신호(sdrock)님이 2021-01-25 14:59에 작성한 댓글입니다.

기존 ID 정렬을 이용하는 부분도 문제는 있습니다.

68 이 383 보다 나중에 출력되는 문제.

원인은 자리수가 달라서 생기는 문제입니다.

해결은 LPAD 등으로 자리수를 동일하게 채워서 연결해 주셔야 합니다.

- 변경전 : 68-379

- 변경후 : 0068-0379

마농(manon94)님이 2021-01-25 16:39에 작성한 댓글입니다.

확인했습니다. 감사합니다.!!

매수신호(sdrock)님이 2021-01-26 20:20에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
31334특정시간이 포함된 시간을 구하고 싶습니다. [2]
피어스
2021-03-29
3889
31333mysqldump 파일명 지정 관련 질문 [2]
왕초보
2021-03-17
3238
31332php 에서 원격 mysql 접속
AALS
2021-03-05
3224
31330재귀호출에 의한 하이락키 구조의 트리형태의 정렬 [4]
김씨
2021-01-22
3639
3132880만건 데이터 조회 쿼리 동시에 요청시 실행시간이 요청수*실행시간 만큼 걸리는 현상... [4]
도와주세요
2021-01-14
3683
31327mac 으로 mariadb 설치 후 데이터베이스 생성이 안됩니다.. [4]
따봉도치
2021-01-05
5092
31326값이 없는 년월 찾기 질문드립니다. [1]
피어스
2020-12-30
3098
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.017초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다