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

Oracle Q&A 40911 게시물 읽기

No. 40911

같은 이름중에서 코드가 일부 상이한 번호 찾기
작성자	초보네	작성일	2015-08-27 18:59	조회수	8,649

안녕하세요.

GROUP BY 를 이용해야 할것 같은데 도저히 머리가 돌아가지 않네요.

한방 쿼리로 가능한지 다른 방향으로 개발을 해야 할지 알려주세요..

NAME 이 같은 것중에서 NUM 에 갯수가 2개 이상이며, CODE 가 일부 틀린 NUM 를 찾는 쿼리입니다.

---- 데이터

WITH DATA AS (

SELECT '우리집' NAME, '101' NUM, 'X01' CODE FROM DUAL

UNION ALL SELECT '우리집' NAME, '101' NUM, 'X02' CODE FROM DUAL

UNION ALL SELECT '이정표' NAME, '201' NUM, 'A01' CODE FROM DUAL

UNION ALL SELECT '이정표' NAME, '201' NUM, 'A02' CODE FROM DUAL

UNION ALL SELECT '이정표' NAME, '202' NUM, 'A01' CODE FROM DUAL

UNION ALL SELECT '이정표' NAME, '202' NUM, 'A02' CODE FROM DUAL

UNION ALL SELECT '간담회' NAME, '301' NUM, 'T01' CODE FROM DUAL

UNION ALL SELECT '간담회' NAME, '301' NUM, 'T02' CODE FROM DUAL

UNION ALL SELECT '간담회' NAME, '302' NUM, 'T01' CODE FROM DUAL

UNION ALL SELECT '간담회' NAME, '302' NUM, 'T04' CODE FROM DUAL

UNION ALL SELECT '간담회' NAME, '303' NUM, 'T01' CODE FROM DUAL

UNION ALL SELECT '간담회' NAME, '303' NUM, 'T05' CODE FROM DUAL

UNION ALL SELECT '휴대폰' NAME, '401' NUM, 'P01' CODE FROM DUAL

UNION ALL SELECT '휴대폰' NAME, '401' NUM, 'P02' CODE FROM DUAL

UNION ALL SELECT '휴대폰' NAME, '402' NUM, 'P03' CODE FROM DUAL

UNION ALL SELECT '휴대폰' NAME, '402' NUM, 'P04' CODE FROM DUAL

UNION ALL SELECT '휴대폰' NAME, '403' NUM, 'P05' CODE FROM DUAL

)

SELECT * FROM DATA

;

1. 우리집 : NUM 가 1개라서 나오지 않는다. 결과 : X

2. 이정표 : NUM 가 2개지만 NUM가 가지는 CODE 값이 동일. 결과 : X

3. 간담회 : NUM 가 3개이고 모든 NUM 가 CODE = T01 일부만 가지고 있음. 결과 : O

4. 휴대폰 : NUM 가 3개지만 NUM가 모두 다른 CODE 를 가지고 있어서 조회되면 안됌. 결과 : X

결과

-------------------------

간담회 301

간담회 302

간담회 303

--------------------------

고수분들에 답변 기다립니다.

감사합니다.

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

코드가 일부 틀리다의 의미가???
- 간담회 의 3개 NUM 이 공통으로 T01 을 가지고 있고 다른코드가 틀린데
- 이렇게 3개 num 이 모두 공통으로 가지고 있어야만 하는 건지?
- 아니면 2개 num 만 공통되어도 인정할 수 있는 것인지?

마농(manon94)님이 2015-08-27 19:28에 작성한 댓글입니다.

먼저 답글 감사합니다.

원래 요구사항은 2개만 공통으로 있어도 일부 틀리다의 의미로 인정할수 있습니다.

그런데 너무 어려워서 .. CODE 가 하나라도 틀리면 해당 NAME 을 다보여주는 것으로 바꾸기로 했습니다.

결국 NUM 에 종속된 CODE 값이 모두 동일한 경우가 아니면 다보여주면 될것 같습니다.

결과는

-----------------

간담회 모두

휴대폰 모두

-----------------

DBS는 MSSQL 2010 을 사용하고 있는데, 급한 마음에 ORACLE 계시판에도 올렸습니다.

양해 부탁드립니다.

감사합니다.

초보네님이 2015-08-28 11:26에 작성한 댓글입니다. Edit Ｘ

WITH data AS
(
SELECT '우리집' name, '101' num, 'X01' code FROM dual
UNION ALL SELECT '우리집', '101', 'X02' FROM dual
UNION ALL SELECT '이정표', '201', 'A01' FROM dual
UNION ALL SELECT '이정표', '201', 'A02' FROM dual
UNION ALL SELECT '이정표', '202', 'A01' FROM dual
UNION ALL SELECT '이정표', '202', 'A02' FROM dual
UNION ALL SELECT '간담회', '301', 'T01' FROM dual
UNION ALL SELECT '간담회', '301', 'T02' FROM dual
UNION ALL SELECT '간담회', '302', 'T01' FROM dual
UNION ALL SELECT '간담회', '302', 'T04' FROM dual
UNION ALL SELECT '간담회', '303', 'T01' FROM dual
UNION ALL SELECT '간담회', '303', 'T05' FROM dual
UNION ALL SELECT '휴대폰', '401', 'P01' FROM dual
UNION ALL SELECT '휴대폰', '401', 'P02' FROM dual
UNION ALL SELECT '휴대폰', '402', 'P03' FROM dual
UNION ALL SELECT '휴대폰', '402', 'P04' FROM dual
UNION ALL SELECT '휴대폰', '403', 'P05' FROM dual
)
SELECT name, num, code
FROM (SELECT name, num, code
             , COUNT(DISTINCT codes) OVER(PARTITION BY name) cnt1
             , MAX(cnt) OVER(PARTITION BY name, num) cnt2
          FROM (SELECT name, num, code
                     , wm_concat(DISTINCT code)
                                  OVER(PARTITION BY name, num) codes
                     , COUNT(num) OVER(PARTITION BY name, code) cnt
                  FROM data
                )
        )
WHERE cnt1 > 1 -- code 가 모두 동일한 것 제외
   AND cnt2 > 1 -- 동일 code 가 하나도 없는 것 제외
;

마농(manon94)님이 2015-08-31 10:27에 작성한 댓글입니다.
이 댓글은 2015-08-31 11:27에 마지막으로 수정되었습니다.

-- MSSQL 용 --

WITH data AS
(
SELECT '우리집' name, '101' num, 'X01' code
UNION ALL SELECT '우리집', '101', 'X02'
UNION ALL SELECT '이정표', '201', 'A01'
UNION ALL SELECT '이정표', '201', 'A02'
UNION ALL SELECT '이정표', '202', 'A01'
UNION ALL SELECT '이정표', '202', 'A02'
UNION ALL SELECT '간담회', '301', 'T01'
UNION ALL SELECT '간담회', '301', 'T02'
UNION ALL SELECT '간담회', '302', 'T01'
UNION ALL SELECT '간담회', '302', 'T04'
UNION ALL SELECT '간담회', '303', 'T01'
UNION ALL SELECT '간담회', '303', 'T05'
UNION ALL SELECT '휴대폰', '401', 'P01'
UNION ALL SELECT '휴대폰', '401', 'P02'
UNION ALL SELECT '휴대폰', '402', 'P03'
UNION ALL SELECT '휴대폰', '402', 'P04'
UNION ALL SELECT '휴대폰', '403', 'P05'
)
SELECT name, num, code
FROM (SELECT name, num, code
             , MIN(codes) OVER(PARTITION BY name) code1
             , MAX(codes) OVER(PARTITION BY name) code2
             , MAX(cnt) OVER(PARTITION BY name, num) cnt
          FROM (SELECT name, num, code
                     , STUFF((SELECT ',' + code
                                FROM data
                               WHERE name = a.name
                                 AND num = a.num
                               ORDER BY code
                                 FOR XML PATH('')
                              ), 1, 1, '') codes
                     , COUNT(num) OVER(PARTITION BY name, code) cnt
                  FROM data a
                ) a
        ) a
WHERE code1 != code2 -- code 가 모두 동일한 것 제외
   AND cnt > 1         -- 동일 code 가 하나도 없는 것 제외
;

마농(manon94)님이 2015-08-31 11:42에 작성한 댓글입니다.
이 댓글은 2015-08-31 11:42에 마지막으로 수정되었습니다.

마농님 감사합니다.

쿼리가 심플하고 이쁘네요. 잘 쓰겠습니다.

오라클만 하다가 첨으로 MS-SQL 을 하다보니 count(distinct) over() 여기서 distinct 가 눈에 들어오네요.

mssql 은 over() 절에서 distinct count 는 안돼나 보네요.

초보네님이 2015-09-02 08:19에 작성한 댓글입니다.
이 댓글은 2015-09-02 09:06에 마지막으로 수정되었습니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
40917	오라클 클라이언트와 엔진의 차이? [2]	초보자	2015-09-01	8120
40916	앞뒤 row 비교하기 [1]	김고운	2015-09-01	8282
40914	erwin 사용법 [1]	가영	2015-08-30	8778
40911	같은 이름중에서 코드가 일부 상이한 번호 찾기 [5]	초보네	2015-08-27	8649
40910	ssh로 sqlplus 실행 시 command not found [1]	새내기	2015-08-27	8705
40909	dba_hist_sqlbind 관련 문의입니다.	이현정	2015-08-27	7698
40906	ROW 정보 가로로 표현하기 [2]	지혀니	2015-08-26	8920