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
운영게시판
최근게시물
MySQL Q&A 31153 게시물 읽기
No. 31153
다음 쿼리를 JOIN으로 해야 하나요? SELECT를 두번으로 해야 하나요?
작성자
쿼리초보
작성일
2018-02-06 12:19:21
조회수
538

안녕하세요. 쿼리초보입니다. 초보적인 질문일 수 있습니다만 답변 부탁드리겠습니다.

 

1:n으로 연결되어 있는 두 테이블 tb1과 tb2가 있습니다. 키는 ky1, ky2, ky3라는 세개의 필드 입니다.

 

tb1에는 키 외에 spent라는 필드가 있으며, tb2에는 별도의 데이터 고유 번호인 sm1, sm2, sm3와 비용금액을 나타내는 cost라는 필드가 있습니다.

즉, tb1의 하나의 ky1, ky2, ky3 조합 튜플은 tb2의 n개의 데이터를 갖으며 이들은 특정 sm1, sm2, sm3를 갖고 있습니다. sm1, sm2, sm3의 조합은 다른 그룹(다른 ky1, ky2, ky3 조합)에는 중복되어 들어갑니다.

 

보여주고 싶은 것은 tb1.ky1, tb1.ky2, tb1.ky3, spent, sum(cost)입니다. 여기서 sum(cost)는 각 ky1, ky2, ky3들의 cost의 합입니다. 여기까지는 다음 쿼리문을 사용하여 보여주도록 하였습니다.

 

SELECT tb1.ky1, tb1.ky2, tb1.ky3, tb1.spent, sum(tb2.cost) FROM tb1 LEFT JOIN tb2 ON (tb1.ky1=tb2.ky1 AND tb1.ky2=tb2.ky2 AND tb1.ky3=tb2.ky3) GROUP BY tb1.ky1, tb1.ky2, tb1.ky3, tb1.spent;

 

그런데 이중 tb2에서 특정 sm1, sm2, sm3의 조합은 갖고 있지만 특정 조합은 갖고 있지 않은 데이터만 보여주고 싶습니다.

즉, tb2.sm1='11' AND tb2.sm2='22' AND tb2.sm3='33'과 NOT (tb2.sm1='00' AND tb2.sm2='00' AND tb2.sm3='00'인 조건을 추가로 넣고 싶습니다.

 

WHERE 절에 조건을 넣으면 제대로된 값이 안나오고, ON 절에 넣으면 그냥 전체하고 값이 갖게 나옵니다. 

 

혹시 이를 검색할 수 있는 쿼리를 아시면 답변 부탁드리겠습니다. 감사합니다.

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

특정 조합은 갖고 있지만, 특정 조합은 갖고 있지 않은 데이터?
애매모호한 표현이네요.
원본 대비 결과 자료를 표형태로 보여주세요.

마농(manon94)님이 2018-02-06 13:29:07에 작성한 댓글입니다.

 테이블은 다음과 같습니다.

tb1은

 

ky1 ky2 ky3 spent
1 12 345 4500
2 34 567 5000
3 45 678 5400

tb2는

 

ky1 ky2 ky3 sm1 sm2 sm3 cost
1 12 345 11 22 33 450
1 12 345 32 34 56 300
2 34 567 11 22 33 330
2 34 567 00 00 00 320
3 45 678 36 34 23 450

tb2.sm1='11' AND tb2.sm2='22' AND tb2.sm3='33'과 NOT (tb2.sm1='00' AND tb2.sm2='00' AND tb2.sm3='00' 조건을 만족하는 데이터는 첫번째 빨간색 부분이므로 다음과 같습니다.

 

ky1 ky2 ky3 spent sum(cost)
1 12 345 4500 750


 두번째는 sm1, sm2, sm3가 00, 00, 00을 포함하고 세번째는 둘다 만족하지 않아서 검색이 되지 않아야 합니다. 

답변 부탁드립니다. 감사합니다.

쿼리초보님이 2018-02-06 15:21:24에 작성한 댓글입니다.
이 댓글은 2018-02-06 15:26:01에 마지막으로 수정되었습니다. Edit

SELECT tb1.ky1
     , tb1.ky2
     , tb1.ky3
     , tb1.spent
     , SUM(tb2.cost) cost
  FROM tb1
 INNER JOIN tb2
    ON tb1.ky1 = tb2.ky1
   AND tb1.ky2 = tb2.ky2
   AND tb1.ky3 = tb2.ky3
 GROUP BY tb1.ky1, tb1.ky2, tb1.ky3, tb1.spent
HAVING COUNT(CASE WHEN sm1='11' AND sm2='22' AND sm3='33' THEN 1 END) > 0
   AND COUNT(CASE WHEN sm1='00' AND sm2='00' AND sm3='00' THEN 1 END) = 0
;

마농(manon94)님이 2018-02-06 17:20:09에 작성한 댓글입니다.
이 댓글은 2018-02-06 17:36:21에 마지막으로 수정되었습니다.

안녕하세요. 답변 감사드립니다. 우선 답변주신 쿼리를 보고 무릎을 탁 칠만큼 빠르게 이해가 돼서 이 쿼리가 맞겠구나 생각을 했습니다.

 

그런데 막상 돌려보니 아무것도 안나오더라구요. 그래서 

AND COUNT(CASE WHEN sm1='00' AND sm2='00' AND sm3='00' THEN 1 END) = 0

이 부분을 빼면 빨간색과 파란색 두 튜플이 나올것이라 예상하고 실행해봤더니 검정색까지 세 튜플이 나오더라구요.

 

JOIN에 문제가 있나 싶어 tb2에서만

SELECT ky1, ky2, ky3, cost

FROM tb2

GROUP BY ky1, ky2, ky3, cost

HAVING COUNT(CASE WHEN sm1='11' AND sm='22' AND sm3='33' THEN 1 END) > 0;

이라고 실행해보니 이 또한 tb2 전체가 나옵니다.

 

그래서 HAVING 절에서 무엇이 문제인지 하나하나 보다보니,  COUNT를 빼고

HAVING CASE WHEN sm1='1'' AND sm2='22' AND sm3='33' THEN 1 END = 1;

로 변경해서 해보니

빨간색에서 11, 22, 33이 들어간 튜플과 파란색에서 11, 22, 33이 들어간 튜플이 나왔습니다.

CASE절만 했을때는 값이 나오는데 COUNT를 쓰면 왜 COUNT 조건이 안먹는것인지 모르겠습니다.

 

참고로 말씀드리면 제가 MS ACCESS에서 데이터를 꺼내는것이라 ACCESS는 CASE WHEN이 안먹는다고 들어서 이대신 SWITCH를 썼습니다. 제가 정확히 사용한 HAVING절은

HAVING COUNT(SWITCH(sm1='11' AND sm2='22' AND sm3='33', 1, true, 0) > 0

과 HAVING SWITCH(sm1='11' AND sm2='22' AND sm3='33', 1, true, 0) = 1입니다.

 

다시한번 친절한 답변 감사드리며, 혹시 무엇이 문제인지 알고계시면 답변 부탁드리겠습니다.

 

 

쿼리초보님이 2018-02-06 19:27:34에 작성한 댓글입니다. Edit

Case 문에는 문제가 없습니다.
Switch 구문을 잘못 적용하신 듯 합니다.
Case 문에서 else 를 사용 하지 않은 것처럼
Switch 문에서 (true, 0) 부분이 없어야 겠네요.


SELECT tb1.ky1
     , tb1.ky2
     , tb1.ky3
     , tb1.spent
     , SUM(tb2.cost) cost
  FROM tb1
 INNER JOIN tb2
    ON tb1.ky1 = tb2.ky1
   AND tb1.ky2 = tb2.ky2
   AND tb1.ky3 = tb2.ky3
 GROUP BY tb1.ky1, tb1.ky2, tb1.ky3, tb1.spent
HAVING COUNT(SWITCH(sm1='11' AND sm2='22' AND sm3='33', 1) > 0
   AND COUNT(SWITCH(sm1='00' AND sm2='00' AND sm3='00', 1) = 0
;

마농(manon94)님이 2018-02-06 22:17:45에 작성한 댓글입니다.
이 댓글은 2018-02-07 07:54:42에 마지막으로 수정되었습니다.

답변 진심으로 감사드립니다.

 

말씀처럼 ELSE 부분이 없어야 하는 군요. ELSE일 경우 0을 리턴하면 어차피 COUNT가 안될텐데 왜 없어야 하는지 잘 이해는 안가지만, 열심히 공부해 보겠습니다.

 

쿼리 작성하시는 능력에 정말 감탄했습니다. 정말 감사드립니다. 좋은하루 되십시오!!!!!

 

오준석님이 2018-02-07 12:14:12에 작성한 댓글입니다. Edit

0 을 리턴하면 왜 카운트가 안된다고 생각하시는지?
합계 낼 때는 0 이 있어도 상관 없지만
카운트 할 때는 0 도 카운트 되지요.

마농(manon94)님이 2018-02-07 13:04:50에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
31157MySQL에서 테이블 JOIN 시 퍼포먼스가 궁금합니다.
김종수
2018-02-08
494
31155제가 메모장에 적어둔 SQL인데 기능을 까먹었습니다..이게 어떤건지좀 봐주세요 [1]
rladmsxor93
2018-02-07
499
31154[질문]컬럼의 합계를 구하는 쿼리를 만들고자 합니다. [1]
신승익
2018-02-06
515
31153다음 쿼리를 JOIN으로 해야 하나요? SELECT를 두번으로 해야 하나요? [7]
쿼리초보
2018-02-06
538
31152[질문]테이블 1에서 테이블2로 자료 넘겨주기 [3]
신승익
2018-01-29
588
31151like 문장 거꾸로 실행하는법 [4]
김양훈
2018-01-25
699
31150날짜와 시간을 토대로 select 하려면 어떻게 해야할까요 ? [1]
asdf
2018-01-24
649
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2017 DSN, All rights reserved.
작업시간: 0.114초, 이곳 서비스는
	PostgreSQL v10.1로 자료를 관리합니다