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 40760 게시물 읽기
No. 40760
교차되는 값 데이타구하기 query 질문
작성자
강형석(htrash)
작성일
2015-03-31 20:12
조회수
8,413
　 사과 배 딸기 수박
사과 x 4 3 4
배 x x 2 3
딸기 x x x 5
수박 x x x x

위와 같은 모양이 query로 가능할까요??
컬럼과 로우에 기준점이 없이 교차되면 카운트되는 구조입니다.
 

테이블은 메인과일 하나가 들어있는 A와 부수과일 여러개가 들어있는 B테이블이
1 : N 구조로 되어있습니다. 테이블 B에는 A에서 선택한 메인과일 값은 가질 수 없고,
메인과일 이외의 과일은 여러개 선택할 수 있습니다.

만약 A(메인과일) 값이 딸기이고 B(부수과일) 값이 사과와 배라면 위에서 컬럼과 로우가
만나는 지점인 3, 2값이 들어있는 곳에 누적되어야 합니다.(x 로 표시되어 있는부분에 카운트 되면 안됨)

메인과일을 기준점으로 하려고 해도 위와같이 좌우가 만나는 지점에 카운트로 표현한다는게
쿼리로 쉽지가 않네요.

많은 가르침 부탁드립니다.
이 글에 대한 댓글이 총 6건 있습니다.

 위의 결과가 나오려면 A와 B에 어떤 데이타가 있어야 하는지 올려주십시오

김흥수(protokhs)님이 2015-04-01 00:24에 작성한 댓글입니다.

김흥수님 관심 가져주셔서 감사합니다.

설명이 부족하고 예제가 없어서 죄송합니다~

 

편의상 aa:사과 bb:배 cc:딸기 dd:수박 이라 가정하고..(코드)

 

with main_t as

(

select '1000' id, 'aa' fr_cd, '사과' fr_nm from dual

union all select '1001' id, 'aa' fr_cd, '사과' fr_nm from dual

union all select '1002' id, 'aa' fr_cd, '사과' fr_nm from dual

union all select '1003' id, 'aa' fr_cd, '사과' fr_nm from dual

 

union all select '1004' id, 'cc' fr_cd, '딸기' fr_nm from dual

union all select '1005' id, 'cc' fr_cd, '딸기' fr_nm from dual

union all select '1006' id, 'cc' fr_cd, '딸기' fr_nm from dual

 

union all select '1007' id, 'dd' fr_cd, '수박' fr_nm from dual

union all select '1008' id, 'dd' fr_cd, '수박' fr_nm from dual

union all select '1009' id, 'dd' fr_cd, '수박' fr_nm from dual

union all select '1010' id, 'dd' fr_cd, '수박' fr_nm from dual

union all select '1011' id, 'dd' fr_cd, '수박' fr_nm from dual

union all select '1012' id, 'dd' fr_cd, '수박' fr_nm from dual

)

 

with sub_t as

( select '1000' id, '1' seq, 'bb' fr_cd, '배' fr_nm from dual

union all select '1001' id, '1' seq, 'bb' fr_cd, '배' fr_nm from dual

union all select '1002' id, '1' seq, 'bb' fr_cd, '배' fr_nm from dual

union all select '1003' id, '1' seq, 'bb' fr_cd, '배' fr_nm from dual

 

union all select '1004' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual

union all select '1004' id, '2' seq, 'bb' fr_cd, '배' fr_nm from dual

 

union all select '1005' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual

union all select '1005' id, '2' seq, 'bb' fr_cd, '배' fr_nm from dual

 

union all select '1006' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual

union all select '1006' id, '2' seq, 'dd' fr_cd, '수박' fr_nm from dual

 

union all select '1007' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual

union all select '1007' id, '2' seq, 'bb' fr_cd, '배' fr_nm from dual

union all select '1007' id, '3' seq, 'cc' fr_cd, '딸기' fr_nm from dual

 

union all select '1008' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual

union all select '1008' id, '2' seq, 'bb' fr_cd, '배' fr_nm from dual

 

union all select '1009' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual

union all select '1009' id, '2' seq, 'bb' fr_cd, '배' fr_nm from dual

 

union all select '1010' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual

union all select '1010' id, '2' seq, 'cc' fr_cd, '딸기' fr_nm from dual

union all select '1011' id, '1' seq, 'cc' fr_cd, '딸기' fr_nm from dual

union all select '1012' id, '1' seq, 'cc' fr_cd, '딸기' fr_nm from dual

)

 

위와같은 예제일때 id로 조인하여 본문의 표와 같이 카운트하는 것이 목적입니다.

예를들어 id 1004일때 메인과일은 딸기, 서브과일은 사과와 배이므로 딸기와 사과가 만나는 지점과 딸기와 배가 만나는 지점에 각각 카운트됩니다.

(본문표의 모든 숫자를 합치면 sub_t 테이블의 건수와 동일합니다.)

 

다시한번 많은 가르침 부탁드립니다~~~

강형석(htrash)님이 2015-04-01 08:47에 작성한 댓글입니다.
이 댓글은 2015-04-01 08:49에 마지막으로 수정되었습니다.

제가 문제를 바로 이해했는지?

메인 과일이 컬럼으로 나오는거면 아래의 쿼리입니다.

 

 with main_t as

(
select '1000' id, 'aa' fr_cd, '사과' fr_nm from dual
union all select '1001' id, 'aa' fr_cd, '사과' fr_nm from dual
union all select '1002' id, 'aa' fr_cd, '사과' fr_nm from dual
union all select '1003' id, 'aa' fr_cd, '사과' fr_nm from dual
 
union all select '1004' id, 'cc' fr_cd, '딸기' fr_nm from dual
union all select '1005' id, 'cc' fr_cd, '딸기' fr_nm from dual
union all select '1006' id, 'cc' fr_cd, '딸기' fr_nm from dual
 
union all select '1007' id, 'dd' fr_cd, '수박' fr_nm from dual
union all select '1008' id, 'dd' fr_cd, '수박' fr_nm from dual
union all select '1009' id, 'dd' fr_cd, '수박' fr_nm from dual
union all select '1010' id, 'dd' fr_cd, '수박' fr_nm from dual
union all select '1011' id, 'dd' fr_cd, '수박' fr_nm from dual
union all select '1012' id, 'dd' fr_cd, '수박' fr_nm from dual
)
, sub_t as
( select '1000' id, '1' seq, 'bb' fr_cd, '배' fr_nm from dual
union all select '1001' id, '1' seq, 'bb' fr_cd, '배' fr_nm from dual
union all select '1002' id, '1' seq, 'bb' fr_cd, '배' fr_nm from dual
union all select '1003' id, '1' seq, 'bb' fr_cd, '배' fr_nm from dual
 
union all select '1004' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual
union all select '1004' id, '2' seq, 'bb' fr_cd, '배' fr_nm from dual
 
union all select '1005' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual
union all select '1005' id, '2' seq, 'bb' fr_cd, '배' fr_nm from dual
 
union all select '1006' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual
union all select '1006' id, '2' seq, 'dd' fr_cd, '수박' fr_nm from dual
 
union all select '1007' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual
union all select '1007' id, '2' seq, 'bb' fr_cd, '배' fr_nm from dual
union all select '1007' id, '3' seq, 'cc' fr_cd, '딸기' fr_nm from dual
 
union all select '1008' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual
union all select '1008' id, '2' seq, 'bb' fr_cd, '배' fr_nm from dual
 
union all select '1009' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual
union all select '1009' id, '2' seq, 'bb' fr_cd, '배' fr_nm from dual
 
union all select '1010' id, '1' seq, 'aa' fr_cd, '사과' fr_nm from dual
union all select '1010' id, '2' seq, 'cc' fr_cd, '딸기' fr_nm from dual
union all select '1011' id, '1' seq, 'cc' fr_cd, '딸기' fr_nm from dual
union all select '1012' id, '1' seq, 'cc' fr_cd, '딸기' fr_nm from dual
)
, 과일조인목록 as
(
    select
        a.fr_cd 메인과일
        , b.fr_cd 서브과일
        , b.fr_nm 서브과일명
    from    main_t a
            , sub_t b
    where   a.id = b.id
)
select
    서브과일
    ,서브과일명
    ,case when 사과 = 0 then '*' else to_char(사과) end 사과
    ,case when 배 = 0 then '*' else to_char(배) end 배
    ,case when 딸기 = 0 then '*' else to_char(딸기) end 딸기
    ,case when 수박 = 0 then '*' else to_char(사과) end 수박
from    과일조인목록
PIVOT
(
   count(*) FOR 메인과일 IN
   (
        'aa' AS 사과
       ,'bb' AS 배
       ,'cc' AS 딸기
       ,'dd' AS 수박
   )
 
)
order by
    서브과일
김흥수(protokhs)님이 2015-04-01 09:40에 작성한 댓글입니다.

김흥수님 답변 감사합니다~~~

현재 사용 db가  pivot 기능도 없는데다가(DB2임... ^^), 메인과일수가 컬럼으로 나오는 형태가 아니라서(중복되면 메인/서브 상관없이 가로/세로 교차점에서 카운트),  천상 query 로는 할 수 없을것 같습니다.

다시한번 고개숙여 감사드립니다~~~(꾸벅)

강형석(htrash)님이 2015-04-01 16:13에 작성한 댓글입니다.

WITH main_t AS
(
SELECT '1000' id, 'aa' fr_cd, '사과' fr_nm FROM dual
UNION ALL SELECT '1001', 'aa', '사과' FROM dual
UNION ALL SELECT '1002', 'aa', '사과' FROM dual
UNION ALL SELECT '1003', 'aa', '사과' FROM dual
UNION ALL SELECT '1004', 'cc', '딸기' FROM dual
UNION ALL SELECT '1005', 'cc', '딸기' FROM dual
UNION ALL SELECT '1006', 'cc', '딸기' FROM dual
UNION ALL SELECT '1007', 'dd', '수박' FROM dual
UNION ALL SELECT '1008', 'dd', '수박' FROM dual
UNION ALL SELECT '1009', 'dd', '수박' FROM dual
UNION ALL SELECT '1010', 'dd', '수박' FROM dual
UNION ALL SELECT '1011', 'dd', '수박' FROM dual
UNION ALL SELECT '1012', 'dd', '수박' FROM dual
)
, sub_t AS
(
SELECT '1000' id, '1' seq, 'bb' fr_cd, '배' fr_nm FROM dual
UNION ALL SELECT '1001', '1', 'bb', '배'   FROM dual
UNION ALL SELECT '1002', '1', 'bb', '배'   FROM dual
UNION ALL SELECT '1003', '1', 'bb', '배'   FROM dual
UNION ALL SELECT '1004', '1', 'aa', '사과' FROM dual
UNION ALL SELECT '1004', '2', 'bb', '배'   FROM dual
UNION ALL SELECT '1005', '1', 'aa', '사과' FROM dual
UNION ALL SELECT '1005', '2', 'bb', '배'   FROM dual
UNION ALL SELECT '1006', '1', 'aa', '사과' FROM dual
UNION ALL SELECT '1006', '2', 'dd', '수박' FROM dual
UNION ALL SELECT '1007', '1', 'aa', '사과' FROM dual
UNION ALL SELECT '1007', '2', 'bb', '배'   FROM dual
UNION ALL SELECT '1007', '3', 'cc', '딸기' FROM dual
UNION ALL SELECT '1008', '1', 'aa', '사과' FROM dual
UNION ALL SELECT '1008', '2', 'bb', '배'   FROM dual
UNION ALL SELECT '1009', '1', 'aa', '사과' FROM dual
UNION ALL SELECT '1009', '2', 'bb', '배'   FROM dual
UNION ALL SELECT '1010', '1', 'aa', '사과' FROM dual
UNION ALL SELECT '1010', '2', 'cc', '딸기' FROM dual
UNION ALL SELECT '1011', '1', 'cc', '딸기' FROM dual
UNION ALL SELECT '1012', '1', 'cc', '딸기' FROM dual
)
, code_t AS
(
SELECT 'aa' fr_cd, '사과' fr_nm FROM dual
UNION ALL SELECT 'bb', '배'   FROM dual
UNION ALL SELECT 'cc', '딸기' FROM dual
UNION ALL SELECT 'dd', '수박' FROM dual
)
SELECT a.fr_nm
     , NVL(b.aa, 0) 사과
     , NVL(b.bb, 0) 배
     , NVL(b.cc, 0) 딸기
     , NVL(b.dd, 0) 수박
  FROM code_t a
  LEFT OUTER JOIN
       (SELECT cd1
             , COUNT(CASE cd2 WHEN 'aa' THEN 1 END) aa
             , COUNT(CASE cd2 WHEN 'bb' THEN 1 END) bb
             , COUNT(CASE cd2 WHEN 'cc' THEN 1 END) cc
             , COUNT(CASE cd2 WHEN 'dd' THEN 1 END) dd
          FROM (-- Oracle SQL --
                -- SELECT LEAST(m.fr_cd, s.fr_cd) cd1
                --      , GREATEST(m.fr_cd, s.fr_cd) cd2
                -- ANSI SQL --
                SELECT CASE WHEN m.fr_cd < s.fr_cd
                            THEN m.fr_cd ELSE s.fr_cd END cd1
                     , CASE WHEN m.fr_cd > s.fr_cd
                            THEN m.fr_cd ELSE s.fr_cd END cd2
                  FROM main_t m
                     , sub_t s
                 WHERE m.id = s.id
                )
         GROUP BY cd1
        ) b
    ON a.fr_cd = b.cd1
 ORDER BY a.fr_cd
;

마농(manon94)님이 2015-04-01 21:40에 작성한 댓글입니다.

마농님 정말 감사합니다~~~~~

이런식으로 구현이 되다니 안될거라 생각했던 제가 좀 창피하네요~ ^^

어쨌든 마농님, 김흥수님 모두 감사드리고 건승하십시오~~~~~~~

 

강형석(htrash)님이 2015-04-02 14:24에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
40763토,일을 제외한 날짜 카운트 질문 [1]
김영희
2015-04-02
7306
40762TREE 구조 SELECT 질문입니다. [2]
이용헌
2015-04-02
7573
40761쿼리 퀴즈입니다(울타리 자르기) [1]
김흥수
2015-04-01
11624
40760교차되는 값 데이타구하기 query 질문 [6]
강형석
2015-03-31
8413
40759ORA-00979 에러에 대한 문의 [1]
이성근
2015-03-31
6758
40756쿼리 퀴즈입니다.(퀵소트 따라하기) [1]
김흥수
2015-03-30
8859
40755쿼리 퀴즈입니다.(시계맞추기) [7]
김흥수
2015-03-30
10034
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.018초, 이곳 서비스는 PostgreSQL v16.4로 자료를 관리합니다