데이터베이스 질문과 답변
by 와일드키드 [2016.07.29 15:59:25]
안녕하세요
아래와 같은 table을
with TEST as (
select '1' A, '사과' B, '대구' C, '2000' D from dual union all
select '1' A, '사과' B, '대구' C, '3000' D from dual union all
select '1' A, '사과' B, '광주' C, '1000' D from dual union all
select '1' A, '사과' B, '광주' C, '4000' D from dual union all
select '2' A, '인삼' B, '파주' C, '1000' D from dual union all
select '2' A, '인삼' B, '파주' C, '2000' D from dual union all
select '2' A, '인삼' B, '파주' C, '3000' D from dual union all
select '2' A, '인삼' B, '금산' C, '2000' D from dual
)
select * from test;
아래와같이 표현하고 싶습니다.(C,D컬럼에 정렬)
| A | B | C | D |
| 1 | 사과 | 광주 | 1000 |
| 4000 | |||
| 대구 | 2000 | ||
| 3000 | |||
| 2 | 인삼 | 금산 | 2000 |
| 파주 | 1000 | ||
| 2000 | |||
| 3000 |
부탁드립니다.
with TEST as ( select '1' A, '사과' B, '대구' C, '2000' D from dual union all select '1' A, '사과' B, '대구' C, '3000' D from dual union all select '1' A, '사과' B, '광주' C, '1000' D from dual union all select '1' A, '사과' B, '광주' C, '4000' D from dual union all select '2' A, '인삼' B, '파주' C, '1000' D from dual union all select '2' A, '인삼' B, '파주' C, '2000' D from dual union all select '2' A, '인삼' B, '파주' C, '3000' D from dual union all select '2' A, '인삼' B, '금산' C, '2000' D from dual ) SELECT DECODE(ANUM, 1, A, NULL) AS A ,DECODE(BNUM, 1, B, NULL) AS B ,DECODE(CNUM, 1, C, NULL) AS C ,D FROM ( SELECT A ,B ,C ,D ,ROW_NUMBER() OVER(PARTITION BY A ORDER BY B,C,D) AS ANUM ,ROW_NUMBER() OVER(PARTITION BY A, B ORDER BY C,D) AS BNUM ,ROW_NUMBER() OVER(PARTITION BY A, B, C ORDER BY D) AS CNUM FROM TEST ) 더 간단한 방법도 있을듯 한데 저는 이정도실력이네요 ㅠㅠ
ABNUM 을 ANUM, BNUM 으로 분리하시는게 맞을 듯.
, ROW_NUMBER() OVER(PARTITION BY a ORDER BY b, c, d) AS anum
, ROW_NUMBER() OVER(PARTITION BY a, b ORDER BY c, d) AS bnum
, ROW_NUMBER() OVER(PARTITION BY a, b, c ORDER BY d) AS cnum
역시. 저는 아직 멀었습니다 ㅠㅠ 감사합니다
두분다 모두 감사합니다. 많은 도움 되었습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.