쿼리 도움을 받고자 글을 올립니다 ㅜㅜ
서로 다른테이블을 조회하여 ROWNUM을 부여해서 그 ROW 맞게 조회하고 싶습니다
예시쿼리는
SELECT *
FROM (SELECT '1' AS ORD_NO, 'NAME' AS NM FROM DUAL
UNION ALL
SELECT '2' AS ORD_NO, 'NAME2' AS NM FROM DUAL
UNION ALL
SELECT '1' AS ORD_NO, 'K' AS CD FROM DUAL
UNION ALL
SELECT '2' AS ORD_NO, 'K2' AS CD FROM DUAL
UNION ALL
SELECT '3' AS ORD_NO, 'K3' AS CD FROM DUAL)
결과는 아래와 나오길 바랍니다.
ORD_NO | NM | CD |
1 | NAME | K |
2 | NAME2 | K2 |
3 | NAME3 |
또는
ORD_NO | NM | CD |
1 | NAME | K |
2 | NAME2 | K2 |
3 | K3 |
고수님들 도와주세영
ORD_NO 3은 CD가 없네요 누락 하신 건지..
예시 쿼리는 초봉님이 만들다 만 건가요??
아니면 테이블 데이터가 저렇다는 것인지..
SELECT ORD_NO, MAX(NM) AS NM, MAX(CD) AS CD FROM (SELECT '1' AS ORD_NO, 'NAME' AS NM, NULL AS CD FROM DUAL UNION ALL SELECT '2' AS ORD_NO, 'NAME2' AS NM, NULL AS CD FROM DUAL UNION ALL SELECT '3' AS ORD_NO, 'NAME3' AS NM, NULL AS CD FROM DUAL UNION ALL SELECT '1' AS ORD_NO, NULL AS NM, 'K' AS CD FROM DUAL UNION ALL SELECT '2' AS ORD_NO, NULL AS NM, 'K2' AS CD FROM DUAL) GROUP BY ORD_NO ORDER BY ORD_NO
양쪽에 값이 모두 있다면 그냥 조인해도 되지만 예시처럼 한쪽은 데이터가 없을 수 있다면
LEFT/RIGHT OUTER JOIN 혹은 FULL OUTER JOIN으로 처리해야합니다.
WITH T1 AS ( SELECT '1' AS ORD_NO, 'NAME' AS NM FROM DUAL UNION ALL SELECT '2' AS ORD_NO, 'NAME2' AS NM FROM DUAL UNION ALL SELECT '3' AS ORD_NO, 'NAME3' AS NM FROM DUAL ) , T2 AS ( SELECT '1' AS ORD_NO, 'K' AS CD FROM DUAL UNION ALL SELECT '2' AS ORD_NO, 'K2' AS CD FROM DUAL ) SELECT T1.ORD_NO, T1.NM, T2.CD FROM T1 FULL OUTER JOIN T2 ON T1.ORD_NO = T2.ORD_NO
그런데 FULL OUTER JOIN 보다는 위에 말씀드린 방법이 성능상 나았던 것 같네요.