데이터베이스 질문과 답변
Table A
ID
1
2
Table B
ID ADDRESS
1 성북동
2 사당동
Table C
ID HOBBY
1 축구
1 탁구
2 웹서핑
이런 테이블이 있다고 할때,
출력결과가
ID ADDRESS HOBBY
1 성북동 축구
1 (공백) 탁구
2 사당동 웹서핑
이런식으로 JOIN을 이용해서 출력이 가능할까요?..
아무리 해바도 저부분에 NULL값이나 공백문자가 들어가게 해야될거 같은데 잘 안되네요 ㅡ,ㅡ
도움을 부탁드립니다~
by 知音
[2010.09.03 18:07:06]
with t1 as (
select 1 cd from dual union all
select 2 cd from dual
), t2 as (
select 1 cd, '성북동' addr from dual union all
select 2 cd, '사당동' addr from dual
), t3 as (
select 1 cd, '축구' dv from dual union all
select 1 cd, '탁구' dv from dual union all
select 2 cd, '웹서핑' dv from dual
)
select cd
, decode(rn, 1, addr) addr
, dv
from (
select a.cd
, b.addr
, c.dv
, row_number() over(partition by a.cd order by 1) rn
from t1 a
, t2 b
, t3 c
where a.cd = b.cd
and a.cd = c.cd
)
select 1 cd from dual union all
select 2 cd from dual
), t2 as (
select 1 cd, '성북동' addr from dual union all
select 2 cd, '사당동' addr from dual
), t3 as (
select 1 cd, '축구' dv from dual union all
select 1 cd, '탁구' dv from dual union all
select 2 cd, '웹서핑' dv from dual
)
select cd
, decode(rn, 1, addr) addr
, dv
from (
select a.cd
, b.addr
, c.dv
, row_number() over(partition by a.cd order by 1) rn
from t1 a
, t2 b
, t3 c
where a.cd = b.cd
and a.cd = c.cd
)
by 손님
[2010.09.03 18:14:48]
답변 감사합니다~^^
) 버튼을 클릭하여 작성 하시면 됩니다.