database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Q&A 7304 게시물 읽기
No. 7304
두 테이블 조회값을 한 쿼리 결과값으로 조합 문의
작성자
초개(k62511)
작성일
2024-03-13 10:09
조회수
147

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.
제목
작성자
작성일
조회
7305매개변수 하나로 2개의 행 나누어 차감
MS보초
2024-05-16
13
7304두 테이블 조회값을 한 쿼리 결과값으로 조합 문의 [2]
초개
2024-03-13
147
7303문자열 구분 [2]
초보
2024-01-05
221
7301텍스트 동적 조합 문의 [5]
초개
2023-12-06
251
7299프로시져 성능 문제
나그네
2023-06-30
427
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.017초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다