안녕하세요. 고민끝에 풀수없어서 이렇게 질문 올립니다..
tableA
type | name
1 | 1
1 | 2
0 | park
0 | jang
tableB
name | id
1 | park
1 | aa
2 | park
2 | lee
3 | park
1) tableA.type이 1일때 tableA.name = tableB.name 를 참조해서 해당하는 tableB.id 들이 나오도록 하고싶습니다.
=> 예상값(park,aa,lee)
2) tableA.type이 0일때에는 tableB와 참조하지않고 그대로 tableA.name가 나오도록 하고싶습니다.
=> 예상값(park,jang)
3) 1,2 에서 중복되는 값은 하나만 나오도록 하고싶습니다.
=> 예상값(park,aa,lee,jang)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | With tableA As ( SELECT 1 TYPE, '1' NAME FROM DUAL UNION ALL SELECT 1 , '2' FROM DUAL UNION ALL SELECT 0 , 'park' FROM DUAL UNION ALL SELECT 0 , 'jang' FROM DUAL ), tableB AS ( SELECT '1' NAME , 'park' ID FROM DUAL UNION ALL SELECT '1' , 'aa' FROM DUAL UNION ALL SELECT '2' , 'park' FROM DUAL UNION ALL SELECT '2' , 'lee' FROM DUAL UNION ALL SELECT '3' , 'park' FROM DUAL) SELECT DISTINCT DECODE(A.TYPE, 1, B.ID, A. NAME ) FROM tableA A FULL OUTER JOIN tableB B ON (A. NAME = B. NAME ) ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | WITH tableA AS ( SELECT '1' type, '1' name FROM dual UNION ALL SELECT '1' , '2' FROM dual UNION ALL SELECT '0' , 'park' FROM dual UNION ALL SELECT '0' , 'jang' FROM dual ) , tableB AS ( SELECT '1' name , 'park' id FROM dual UNION ALL SELECT '1' , 'aa' FROM dual UNION ALL SELECT '2' , 'park' FROM dual UNION ALL SELECT '2' , 'lee' FROM dual UNION ALL SELECT '3' , 'park' FROM dual ) SELECT DISTINCT NVL(b.id, a. name ) name FROM tableA a , tableB b WHERE b. name (+) = DECODE(a.type, '1' , a. name ) ; |