데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

WITH tbl_a AS (
SELECT '1' AS code , '11' AS name
UNION ALL
SELECT '2' AS code , '22' AS name
UNION ALL
SELECT '3' AS code , '33' AS name
UNION ALL
SELECT '4' AS code , '44' AS name
UNION ALL
SELECT '5' AS code , '55' AS name
)

WITH tbl_b AS (
SELECT '6' AS code , '66' AS name
UNION ALL
SELECT '7' AS code , '77' AS name
UNION ALL
SELECT '8' AS code , '88' AS name
)

위처럼 두 테이블이 있다고 가정할때
결과값을 아래와같이 출력하려면 어떻게 쿼리를 만들어야할까요..

a_code	a_name	b_code	b_name
1	11	6	66
2	22	7	77
3	33	8	88
4	44
5	55

이 글에 대한 댓글이 총 2건 있습니다.

WITH tbl_a AS (
SELECT '1' AS code , '11' AS name
UNION ALL
SELECT '2' AS code , '22' AS name
UNION ALL
SELECT '3' AS code , '33' AS name
UNION ALL
SELECT '4' AS code , '44' AS name
UNION ALL
SELECT '5' AS code , '55' AS name
), tbl_b AS (
SELECT '6' AS code , '66' AS name
UNION ALL
SELECT '7' AS code , '77' AS name
UNION ALL
SELECT '8' AS code , '88' AS name
)
select *
from (
select
row_number() over (order by ta.code) as rid,
ta.code, ta.name
from tbl_a ta
) a
left outer join (
select
row_number() over (order by tb.code) as rid,
tb.code, tb.name
from tbl_b tb
) b
on a.rid = b.rid

.님이 2024-03-13 11:16에 작성한 댓글입니다. Edit Ｘ

WITH tbl_a AS

(

SELECT '1' code, '11' name

UNION ALL SELECT '2', '22'

UNION ALL SELECT '3', '33'

UNION ALL SELECT '4', '44'

UNION ALL SELECT '5', '55'

)

, tbl_b AS

(

SELECT '6' code, '66' name

UNION ALL SELECT '7', '77'

UNION ALL SELECT '8', '88'

)

SELECT ISNULL(a.rn, b.rn) rn

     , a.code

     , a.name

     , b.code

     , b.name

  FROM (SELECT ROW_NUMBER() OVER(ORDER BY code) rn

             , code

             , name

          FROM tbl_a

        ) a

  FULL OUTER JOIN

       (SELECT ROW_NUMBER() OVER(ORDER BY code) rn

             , code

             , name

          FROM tbl_b

        ) b

    ON a.rn = b.rn

 ORDER BY rn

;

마농(manon94)님이 2024-03-13 13:02에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
7304	두 테이블 조회값을 한 쿼리 결과값으로 조합 문의 [2]	초개	2024-03-13	114
7303	문자열 구분 [2]	초보	2024-01-05	196
7301	텍스트 동적 조합 문의 [5]	초개	2023-12-06	223
7299	프로시져 성능 문제	나그네	2023-06-30	398

작업시간: 0.029초, 이곳 서비스는
PostgreSQL v16.2로 자료를 관리합니다