데이터베이스 질문과 답변
[질문]테이블을 참조하여 결과값을 ( '1' -> 감자 ) 변경하는 방법을 찾고있어요
0
4
706
by 빠쿄 [2009.09.23 13:23:10]
도저히 안되서 질문을 올립니다 ㅠㅠ
| 이름 | 기록번호 | 코드1 | 코드2 | 코드3 |
| 박스A | 10 | 1 | 2 | 3 |
| 박스A | 11 | 4 | 1 | |
| 박스B | 3 | 2 | 4 |
| 코드번호 | 설명 |
| 1 | 사과 |
| 2 | 배 |
| 3 | 감 |
| 4 | 고구마 |
원하는 결과값은
첫번째 테이블에서 1~4 가 코드설명으로 대체되는것 입니다...
박스A 사과 배 감
박스A 고구마 사과
박스A 배 고구마
이런 결과를 얻고 싶어서
테이블 두개를 조인 시켰는데 조건식을
AND( 코드1 = 코드번호 OR
코드2 = 코드번호 OR
코드3 = 코드번호 OR
코드4 = 코드번호 ) 로 주었습니다.
그랬더니 ROW가 늘어나더라구요.. SELECT 구문과 DISTINCT 를 다양하게 사용하여 바꿔 보았지만 원하는 결과를 얻기가 힘들었습니다...어떻게 해야 해결할 수 있을까요
by 서성우
[2009.09.23 13:36:34]
WITH test AS
(
SELECT '박스A' nam , '10' no , '1' code1 , '2' code2 , '3' code3 FROM dual UNION ALL
SELECT '박스A' , '11' , '4' , NULL , '1' FROM dual UNION ALL
SELECT '박스B' , '3' , NULL , '2' , '4' FROM dual
)
, test2 AS
(
SELECT '1' code , '사과' cont FROM dual UNION ALL
SELECT '2' , '배' FROM dual UNION ALL
SELECT '3' , '감' FROM dual UNION ALL
SELECT '4' , '고구마' FROM dual
)
SELECT a.nam ,
a.no ,
Min(Decode(a.lv,1,b.cont)) code1,
Min(Decode(a.lv,2,b.cont)) code2,
Min(Decode(a.lv,3,b.cont)) code3
FROM
(SELECT a.nam ,
a.no ,
Decode(lv,1,code1,2,code2,3,code3) code,
lv
FROM test a, (SELECT LEVEL lv FROM dual
CONNECT BY LEVEL <= 3) b
) a , test2 b
WHERE a.code = b.code
GROUP BY a.nam , a.no
(
SELECT '박스A' nam , '10' no , '1' code1 , '2' code2 , '3' code3 FROM dual UNION ALL
SELECT '박스A' , '11' , '4' , NULL , '1' FROM dual UNION ALL
SELECT '박스B' , '3' , NULL , '2' , '4' FROM dual
)
, test2 AS
(
SELECT '1' code , '사과' cont FROM dual UNION ALL
SELECT '2' , '배' FROM dual UNION ALL
SELECT '3' , '감' FROM dual UNION ALL
SELECT '4' , '고구마' FROM dual
)
SELECT a.nam ,
a.no ,
Min(Decode(a.lv,1,b.cont)) code1,
Min(Decode(a.lv,2,b.cont)) code2,
Min(Decode(a.lv,3,b.cont)) code3
FROM
(SELECT a.nam ,
a.no ,
Decode(lv,1,code1,2,code2,3,code3) code,
lv
FROM test a, (SELECT LEVEL lv FROM dual
CONNECT BY LEVEL <= 3) b
) a , test2 b
WHERE a.code = b.code
GROUP BY a.nam , a.no
by 서성우
[2009.09.23 13:46:38]
WITH test AS
(
SELECT '박스A' nam , '10' no , '1' code1 , '2' code2 , '3' code3 FROM dual UNION ALL
SELECT '박스A' , '11' , '4' , NULL , '1' FROM dual UNION ALL
SELECT '박스B' , '3' , NULL , '2' , '4' FROM dual
)
, test2 AS
(
SELECT '1' code , '사과' cont FROM dual UNION ALL
SELECT '2' , '배' FROM dual UNION ALL
SELECT '3' , '감' FROM dual UNION ALL
SELECT '4' , '고구마' FROM dual
)
SELECT a.nam ,
a.no ,
b.cont ,
c.cont ,
d.cont
FROM test a , test2 b , test2 c , test2 d
WHERE (a.code1 = b.code(+)
AND a.code2 = c.code(+)
AND a.code3 = d.code(+))
(
SELECT '박스A' nam , '10' no , '1' code1 , '2' code2 , '3' code3 FROM dual UNION ALL
SELECT '박스A' , '11' , '4' , NULL , '1' FROM dual UNION ALL
SELECT '박스B' , '3' , NULL , '2' , '4' FROM dual
)
, test2 AS
(
SELECT '1' code , '사과' cont FROM dual UNION ALL
SELECT '2' , '배' FROM dual UNION ALL
SELECT '3' , '감' FROM dual UNION ALL
SELECT '4' , '고구마' FROM dual
)
SELECT a.nam ,
a.no ,
b.cont ,
c.cont ,
d.cont
FROM test a , test2 b , test2 c , test2 d
WHERE (a.code1 = b.code(+)
AND a.code2 = c.code(+)
AND a.code3 = d.code(+))
by 러드
[2009.09.23 13:48:17]
WITH test AS
(
SELECT '박스A' nam , '10' no , '1' code1 , '2' code2 , '3' code3 FROM dual UNION ALL
SELECT '박스A' , '11' , '4' , NULL , '1' FROM dual UNION ALL
SELECT '박스B' , '3' , NULL , '2' , '4' FROM dual
)
, test2 AS
(
SELECT '1' code , '사과' cont FROM dual UNION ALL
SELECT '2' , '배' FROM dual UNION ALL
SELECT '3' , '감' FROM dual UNION ALL
SELECT '4' , '고구마' FROM dual
)
SELECT nam
, no
, ( SELECT cont FROM test2 WHERE code = code1 ) as tt
, ( SELECT cont FROM test2 WHERE code = code2 ) as tt
, ( SELECT cont FROM test2 WHERE code = code3 ) as tt
FROM test
(
SELECT '박스A' nam , '10' no , '1' code1 , '2' code2 , '3' code3 FROM dual UNION ALL
SELECT '박스A' , '11' , '4' , NULL , '1' FROM dual UNION ALL
SELECT '박스B' , '3' , NULL , '2' , '4' FROM dual
)
, test2 AS
(
SELECT '1' code , '사과' cont FROM dual UNION ALL
SELECT '2' , '배' FROM dual UNION ALL
SELECT '3' , '감' FROM dual UNION ALL
SELECT '4' , '고구마' FROM dual
)
SELECT nam
, no
, ( SELECT cont FROM test2 WHERE code = code1 ) as tt
, ( SELECT cont FROM test2 WHERE code = code2 ) as tt
, ( SELECT cont FROM test2 WHERE code = code3 ) as tt
FROM test
by 빠쿄
[2009.09.23 14:22:36]
서성우님 러드님 너무 감사합니다.!! 덕분에 해결 했어요!!!
) 버튼을 클릭하여 작성 하시면 됩니다.