데이터베이스 질문과 답변
안녕하세요 공부하다가 도저히 안되겠다 싶어 질문 드려요
계층구조로 만든 테이블이 있습니다
| 코드 | 명 | 레벨 | 부모코드 | .. |
| A | 사과 | 1 | null | |
| B | 배 | 2 | A | |
| C | 귤 | 3 | B | |
| D | 감 | 2 | A |
이런식으로 있는데 이 테이블을 참고해서 데이터가
| val1 | val2 | val3 | ... | |
| 사과 | 사과 | 사과 | ||
| 사과 | 배 | 배 | ||
| 사과 | 배 | 귤 | ||
| 사과 | 감 | 감 |
이런식으로 상위 레벨 데이터 > 하위레벨데이터 순으로 출력되는데
더이상 하위레벨이 없을경우 마지막 값을 최하위레벨까지 계속 출력하고 싶습니다.
SELECT T.이름,
CASE WHEN 레벨 = 3 THEN T1.이름
WHEN 레벨 < 3 THEN T.이름
END,
CASE WHEN 레벨 = 3 THEN T2.이름
WHEN 레벨 = 2 THEN T1.이름
WHEN 레벨 = 1 THEN T.이름
END,
FROM T
LEFT OUTER JOIN T T1
ON T.부모코드 = T1.코드
LEFT OUTER JOIN T T2
ON T1.부모코드 = T2.코드
레벨이 올라갈수록 조인을 한번씩 더 해줘야하고 CASE별로 해당 레벨조건을 하나씩 더 추가해줘야 하네요
뭔가 다른방법이 있을까요?
by jkson
[2017.03.30 10:47:43]
동적쿼리를 쓰지 않는 이상 유동적으로 나타낼 열을 늘이고 줄이긴 힘들고
나타내야할 max level을 지정하셔야할 듯합니다.
with t as
(
select 'A' code, '사과' nm, 1 lv, null upcode from dual union all
select 'B' code, '배' nm, 2 lv, 'A' upcode from dual union all
select 'C' code, '귤' nm, 3 lv, 'B' upcode from dual union all
select 'D' code, '감' nm, 2 lv, 'A' upcode from dual
)
select lv1,lv2,lv3, lv4, lv5
from
(
select rn, nvl(code,max(code) over(partition by rn order by lv)) code, lv
from
(
select rn, regexp_substr(path,'[^,]+',1,lv) code, lv
from
(
select rownum rn, substr(sys_connect_by_path(code,','),2) path
from t
start with upcode is null
connect by prior code = upcode
) a,
(select level lv from dual connect by level <= 5)--나타낼 level만큼 나열
)
)
pivot (max(code) for lv in (1 as lv1,2 as lv2,3 as lv3, 4 as lv4, 5 as lv5))--나타낼 level 만큼 나열
추가) 더 간단히 해보려고 했는데 안 간단해지네요.. 실력이 없어서..
select *
from
(
select rn, regexp_substr(path,'[^,]+',1,lv) code, lv
from
(
select rn, substr(rpad(path,10,','||substr(path,-1)),1,9) path, lv
from
(
select rownum rn, substr(sys_connect_by_path(code,','),2) path
from t
start with upcode is null
connect by prior code = upcode
)
,(select level lv from dual connect by level <= 5)
)
)
pivot(max(code) for lv in (1 as lv1, 2 as lv2, 3 as lv3, 4 as lv4, 5 as lv5))
첫번째 거 잘못 적었네요ㅎㅎ
nvl(code,max(code) over(partition by rn order by rn)) code
-> nvl(code,max(code) over(partition by rn order by lv)) code
by 잡초
[2017.03.30 11:10:22]
오 답변 감사드려요
주신 쿼리 보고 다시 공부해봐야겠어요
by 마농
[2017.03.30 14:38:58]
WITH t AS
(
SELECT 'A' cd, '사과' nm, '' pcd FROM dual
UNION ALL SELECT 'B', '배', 'A' FROM dual
UNION ALL SELECT 'C', '귤', 'B' FROM dual
UNION ALL SELECT 'D', '감', 'A' FROM dual
)
SELECT NVL(REGEXP_SUBSTR(x, '[^,]+', 1, 1), nm) nm1
, NVL(REGEXP_SUBSTR(x, '[^,]+', 1, 2), nm) nm2
, NVL(REGEXP_SUBSTR(x, '[^,]+', 1, 3), nm) nm3
, NVL(REGEXP_SUBSTR(x, '[^,]+', 1, 4), nm) nm4
, NVL(REGEXP_SUBSTR(x, '[^,]+', 1, 5), nm) nm5
FROM (SELECT nm
, SYS_CONNECT_BY_PATH(nm, ',') x
FROM t
START WITH pcd IS NULL
CONNECT BY PRIOR cd = pcd
)
;
by 잡초
[2017.03.30 16:50:29]
오 이렇게 간단하게 된다니 신기하네요
감사합니다!
by jkson
[2017.03.30 14:54:56]
아..그렇네요. 어차피 nm이 마지막 값이니.. 저는 역시 멍청멍청열매를 따먹은 게 틀림 없습니다ㅜ
) 버튼을 클릭하여 작성 하시면 됩니다.