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
운영게시판
최근게시물
Oracle Q&A 41804 게시물 읽기
No. 41804
컬럼별 데이터 조합?
작성자
궁금이
작성일
2020-06-19 18:08
조회수
584

안녕하세요. 

하루종일 DB사랑넷과 구루비에서 찾아봤는데 검색 단어를 몰라 결국 찾는데 실패했습니다. (ㅠ.ㅠ)

A, B 두 테이블이 있습니다. AAA, BBB, CCC(대,중,소) 라는 컬럼명이 존재하는데 A테이블을 기준으로 B 테이블 정보를 불러옵니다.

그런데 B 테이블과 A 테이블에 AAA 라는 컬럼명에 동일 데이터가 존재하면 해당 데이터만 표현하고 BBB, CCC는 NULL 로 표현합니다. 물론 A테이블은 전부 표현을 합니다.

AAA, BBB 가 존재하면 AAA, BBB를 표현합니다.

제가 열심히 삽질해서 결국 얻은 결론은 스칼라 쿼리밖에 생각이 나질 않아서 하기 처럼 작성하였습니다.

그런데 아무리 생각해도 대,중,소를 스칼라 쿼리로 가져오는 것은 아닌것 같아서요. 데이터가 한두건도 아니고...

그래서 고수님들의 도움을 받고자 합니다.

오라클 버전은 11G 입니다.

WITH BASE AS

 

(

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'CU' AS LLLL, 'LK' AS MMMM, 'NSM' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'FNM' AS MMMM, 'NSM' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_FNS' AS MMMM, 'CEL' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_HS' AS MMMM, 'SU3' AS SSSS FROM DUAL  UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'ASC' AS LLLL, 'ESD' AS MMMM, 'FFA' AS SSSS FROM DUAL  

 

 ),

 SUB AS

(

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'CU' AS LLLL, 'CU_E' AS MMMM, 'SAB' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'CU' AS LLLL, 'FOUP' AS MMMM, 'UP' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'CU' AS LLLL, 'CU_F' AS MMMM, 'NS' AS SSSS FROM DUAL UNION ALL

 

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'FNM' AS MMMM, 'NSM' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'FNM' AS MMMM, 'NSM' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'FNM' AS MMMM, 'NSMD' AS SSSS FROM DUAL UNION ALL

 

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_HS' AS MMMM, 'NSD' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_HS' AS MMMM, 'NSE' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_HS' AS MMMM, 'NSG' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'DES' AS LLLL, 'S_HS' AS MMMM, 'NSG' AS SSSS FROM DUAL 

 )

 SELECT A.PPPP, A.RRRR, A.LLLL, A.MMMM, A.SSSS

          ,(SELECT MAX(LLLL) FROM SUB WHERE A.PPPP = PPPP AND A.RRRR = RRRR AND A.LLLL = LLLL ) AS B_LLLL

          ,(SELECT MAX(MMMM) FROM SUB WHERE A.PPPP = PPPP AND A.RRRR = RRRR AND A.LLLL = LLLL AND A.MMMM = MMMM) AS B_LLLL

          ,(SELECT MAX(SSSS) FROM SUB WHERE A.PPPP = PPPP AND A.RRRR = RRRR AND A.LLLL = LLLL AND A.MMMM = MMMM AND A.SSSS = SSSS) AS B_LLLL

 FROM BASE A

   ORDER BY A.PPPP, A.RRRR, A.LLLL, A.MMMM, A.SSSS

감사합니다.

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

설명하신 내용으로 봐서는 쿼리가 크게 개선되기는 힘들 것으로 보입니다.

아래와 같은 쿼리로도 동일한 결과를 얻어 올 수 있습니다.

참고만 하세요.

SELECT DISTINCT A.PPPP, A.RRRR, A.LLLL, A.MMMM, A.SSSS
       ,L.LLLL, M.MMMM, S.SSSS
FROM BASE A
LEFT JOIN SUB L ON A.PPPP = L.PPPP AND A.RRRR = L.RRRR AND A.LLLL = L.LLLL
LEFT JOIN SUB M ON A.PPPP = M.PPPP AND A.RRRR = M.RRRR AND A.LLLL = M.LLLL AND A.MMMM = M.MMMM
LEFT JOIN SUB S ON A.PPPP = S.PPPP AND A.RRRR = S.RRRR AND A.LLLL = S.LLLL AND A.MMMM = S.MMMM AND A.SSSS = S.SSSS
ORDER BY A.PPPP, A.RRRR, A.LLLL, A.MMMM, A.SSSS 
박인호(paerae)님이 2020-06-23 10:07에 작성한 댓글입니다.

 감사합니다.

 다음과 같이 해결하였더니 속도 개선이 되었습니다.

 

 

(

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'CU' AS LLLL, 'LK' AS MMMM, 'NSM' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'FNM' AS MMMM, 'NSM' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_FNS' AS MMMM, 'CEL' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_HS' AS MMMM, 'SU3' AS SSSS FROM DUAL  UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'ASC' AS LLLL, 'ESD' AS MMMM, 'FFA' AS SSSS FROM DUAL  

 

 ),

 SUB AS

(

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'CU' AS LLLL, 'CU_E' AS MMMM, 'SAB' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'CU' AS LLLL, 'FOUP' AS MMMM, 'UP' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'CU' AS LLLL, 'CU_F' AS MMMM, 'NS' AS SSSS FROM DUAL UNION ALL

 

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'FNM' AS MMMM, 'NSM' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'FNM' AS MMMM, 'NSM' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'FNM' AS MMMM, 'NSMD' AS SSSS FROM DUAL UNION ALL

 

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_HS' AS MMMM, 'NSD' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_HS' AS MMMM, 'NSE' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_HS' AS MMMM, 'NSG' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'DES' AS LLLL, 'S_HS' AS MMMM, 'NSG' AS SSSS FROM DUAL 

 )

 SELECT PPPP, RRRR, LLLL, MMMM, SSSS

         ,MAX(LLLLLLLLLLLLLLLL)

         ,MAX(MMMMMMMMMMMMMM)

         ,MAX(SSSSSSSSSSSSSSSS)

 FROM

 (

 SELECT A.PPPP, A.RRRR, A.LLLL, A.MMMM, A.SSSS

      --     ,B.LLLL, B.MMMM, B.SSSS

           ,(CASE WHEN A.LLLL = B.LLLL THEN B.LLLL

                     ELSE '' END) AS LLLLLLLLLLLLLLLL

           ,(CASE WHEN A.LLLL = B.LLLL AND A.MMMM = B.MMMM  THEN B.MMMM

                     ELSE '' END) AS MMMMMMMMMMMMMM

           ,(CASE WHEN A.LLLL = B.LLLL  AND A.MMMM = B.MMMM 

                     AND A.SSSS = B.SSSS THEN B.SSSS

                     ELSE '' END) AS SSSSSSSSSSSSSSSS

 FROM BASE A

          FULL OUTER JOIN SUB B

          ON A.PPPP = B.PPPP AND A.RRRR = B.RRRR

          AND A.LLLL = B.LLLL

    WHERE A.PPPP IS NOT NULL

  --  GROUP BY A.PPPP, A.RRRR, A.LLLL, A.MMMM, A.SSSS

   --ORDER BY A.PPPP, A.RRRR, A.LLLL, A.MMMM, A.SSSS

   )

   GROUP BY PPPP, RRRR, LLLL, MMMM, SSSS

 

궁금이님이 2020-06-23 19:04에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
41807오라클에서 마스킹 처리 확인 부탁드립니다. [2]
김세익
2020-07-24
2763
41806ORA-01465 : 16진수의 지정이 부적합합니다 [3]
어거스트
2020-07-21
362
41805두행의 차이값 계산하는 쿼리 [2]
궁금
2020-07-21
387
41804컬럼별 데이터 조합? [2]
궁금이
2020-06-19
584
41803게시판을 짜고 primary key가 걸려있는 [2]
이대연
2020-06-05
578
41802테이블에 전문데이타와 전문 레이아웃을 관리
정희철
2020-06-05
438
41800Procedure의 Cursor와 DB Link의 Close 관련 [3]
jmh
2020-06-04
569
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2020 DSN, All rights reserved.
작업시간: 0.044초, 이곳 서비스는
	PostgreSQL v13.0으로 자료를 관리합니다