데이터베이스 질문과 답변
아무 키도 없는 테이블 2개가 있습니다.
| col1 | col2 |
| A | 0 |
| B | 1 |
| col1 | col2 |
| AA | 0 |
| BB | 1 |
| CC | 2 |
| DD | 3 |
이 두테이블을 합쳐서 결과값을 아래처럼 하고싶은데 잘안되요;;;
| COL1 | COL2 | COL3 | COL4 |
| A | 0 | AA | 0 |
| B | 1 | BB | 1 |
| CC | 2 | ||
| DD | 3 |
by jkson
[2017.10.19 11:53:17]
with t1(col1, col2) as
(
select 'A', 0 from dual union all
select 'B', 1 from dual
)
, t2(col1, col2) as
(
select 'AA', 0 from dual union all
select 'BB', 1 from dual union all
select 'CC', 2 from dual union all
select 'DD', 3 from dual
)
select max(col1) col, max(col2) col2, max(col3) col3, max(col4) col4
from
(
select col1, col2, null col3, null col4
, row_number() over (order by col1) rn --정렬기준, 기준 없으면 rownum
from t1
union all
select null col1, null col2, col1 col3, col2 col4
, row_number() over (order by col1) rn --정렬기준, 기준 없으면 rownum
from t2
)
group by rn
order by rn
by DISTINCT
[2017.10.19 12:46:12]
크윽...........캄사합니다.... 아...ROW_NUMBER 로 GROUP BY....
by 우리집아찌
[2017.10.19 14:14:34]
with t1(col1, col2) as
(
select 'A', 0 from dual union all
select 'B', 1 from dual
)
, t2(col1, col2) as
(
select 'AA', 0 from dual union all
select 'BB', 1 from dual union all
select 'CC', 2 from dual union all
select 'DD', 3 from dual
)
SELECT T1.col1
, T1.col2
, T2.col1 AS col3
, T2.col2 col4
FROM T2
, T1
WHERE T2.col2 = T1.col2(+)
by 우리집아찌
[2017.10.19 14:45:41]
/* 항상 답에만 맞추는 ... 아찌 */
with t1(col1, col2) as
(
select 'A', 0 from dual union all
select 'B', 1 from dual
)
, t2(col1, col2) as
(
select 'AA', 0 from dual union all
select 'BB', 1 from dual union all
select 'CC', 2 from dual union all
select 'DD', 3 from dual
)
SELECT T1.col1
, T1.col2
, T2.col1 AS col3
, T2.col2 col4
FROM T1
FULL OUTER JOIN
T2
ON T1.col2 = T2.col2
) 버튼을 클릭하여 작성 하시면 됩니다.