데이터베이스 질문과 답변
오랫만에 질문 드립니다.
특정 테이블에 아래와 같이 데이터가 입력되어 있습니다.
| LOC_ID | LOC_LEVEL | PARENT_LOC_ID | LOC_NAME |
| 1 | 1 | 대한민국 | |
| 2 | 2 | 1 | 서울 |
| 3 | 3 | 2 | 종로구 |
| 4 | 4 | 3 | 매장_1 |
| 5 | 4 | 3 | 매장_2 |
위와 같이 입력되어 있는 테이블에서 SQL 한방으로 아래와 같이 출력을 하려니 좀 애를 먹네요.
| LOC_ID | LOC_LEVEL_1_NAME | LOC_LEVEL_2_NAME | LOC_LEVEL_3_NAME | LOC_LEVEL_4_NAME |
| 1 | 대한민국 | |||
| 2 | 대한민국 | 서울 | ||
| 3 | 대한민국 | 서울 | 종로구 | |
| 4 | 대한민국 | 서울 | 종로구 | 매장_1 |
| 5 | 대한민국 | 서울 | 종로구 | 매장_2 |
Oracle에서는 Connect By를 사용하면 어떻게 될것 같은데 MySQL 5.7 버전이라 쉽지 않네요..ㅜㅜ
전체 건수는 약 20만건 정도됩니다.
고수님들의 고견 부탁드립니다.
by 마농
[2020.06.22 08:55:55]
계층구조가 성립하려면 (코드, 부모코드) 구조여야 합니다.
그런데 부모코드가 안보이네요?
by XX큰타이거
[2020.06.22 10:30:58]
급하게 올리는 바람에 제가 실수를 했네요..ㅜㅜ
원문 수정을 했습니다.
감사합니다. ㅜㅜ
by 마농
[2020.06.22 14:08:26]
WITH t AS
(
SELECT 1 loc_id, 1 loc_level, null parent_loc_id, '대한민국' loc_name
UNION ALL SELECT 2, 2, 1, '서울'
UNION ALL SELECT 3, 3, 2, '종로구'
UNION ALL SELECT 4, 4, 3, '매장_1'
UNION ALL SELECT 5, 4, 3, '매장_2'
)
SELECT t1.loc_id loc_id_lv1
, null loc_id_lv2
, null loc_id_lv3
, null loc_id_lv4
, t1.loc_name loc_name_lv1
, null loc_name_lv2
, null loc_name_lv3
, null loc_name_lv4
FROM t t1
WHERE t1.loc_level = 1
UNION ALL
SELECT t1.loc_id loc_id_lv1
, t2.loc_id loc_id_lv2
, null loc_id_lv3
, null loc_id_lv4
, t1.loc_name loc_name_lv1
, t2.loc_name loc_name_lv2
, null loc_name_lv3
, null loc_name_lv4
FROM t t1
INNER JOIN t t2 ON t2.parent_loc_id = t1.loc_id
WHERE t1.loc_level = 1
UNION ALL
SELECT t1.loc_id loc_id_lv1
, t2.loc_id loc_id_lv2
, t3.loc_id loc_id_lv3
, null loc_id_lv4
, t1.loc_name loc_name_lv1
, t2.loc_name loc_name_lv2
, t3.loc_name loc_name_lv3
, null loc_name_lv4
FROM t t1
INNER JOIN t t2 ON t2.parent_loc_id = t1.loc_id
INNER JOIN t t3 ON t3.parent_loc_id = t2.loc_id
WHERE t1.loc_level = 1
UNION ALL
SELECT t1.loc_id loc_id_lv1
, t2.loc_id loc_id_lv2
, t3.loc_id loc_id_lv3
, t4.loc_id loc_id_lv4
, t1.loc_name loc_name_lv1
, t2.loc_name loc_name_lv2
, t3.loc_name loc_name_lv3
, t4.loc_name loc_name_lv4
FROM t t1
INNER JOIN t t2 ON t2.parent_loc_id = t1.loc_id
INNER JOIN t t3 ON t3.parent_loc_id = t2.loc_id
INNER JOIN t t4 ON t4.parent_loc_id = t3.loc_id
WHERE t1.loc_level = 1
ORDER BY 1, 2, 3, 4
;
by XX큰타이거
[2020.06.23 10:44:13]
앞선 개발사가 모델링은 좀 이상하게해서 조직 구조를 view를 하나 만들어 놨는데 이게 엄청나게 시간을 잡아먹고 있었거든요..
마농님 덕분에 요 방법 응용해서 view를 수정해 봐야 겠습니다.
감사합니다.
한 수 배우고 갑니다.
by 마농
[2020.06.23 11:03:01]
모뎅링을 이상하게 한건 아니고, 계층 구조 테이블 기본 모델입니다.
다만, 계층 쿼리를 지원하는 DB 가 아닌 경우 구현이 어렵기 때문에.
구현하기 쉽도록 반정규화 하는 부분이 없는 거네요.
MySQL 도 최신 버전을 이용한다면 계층쿼리가 지원됩니다.
by XX큰타이거
[2020.06.24 12:43:21]
저 구조는 제가 좀 수정을 한 구조구요.
타 시스템에서 Data Migration 하면서 조직 테이블에 부모Key도 없이 별도의 테이블에 본인 포함 하위 조직의 key값을 갖고있는 구조로 만들어 놨더라구요.
| LOC_ID | REF_LOC_ID |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
| 3 | 3 |
| 3 | 4 |
| 3 | 5 |
| 4 | 4 |
| 5 | 5 |
이런 식으로요... ㅜㅜ
마농님 답변 도움 많이 됐습니다. 감사합니다.ㅎ