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

MySQL Q&A 31153 게시물 읽기

No. 31153

다음 쿼리를 JOIN으로 해야 하나요? SELECT를 두번으로 해야 하나요?
작성자	쿼리초보	작성일	2018-02-06 12:19	조회수	4,847

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

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에 작성한 댓글입니다.

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

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에 작성한 댓글입니다.
이 댓글은 2018-02-06 15:26에 마지막으로 수정되었습니다. 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에 작성한 댓글입니다.
이 댓글은 2018-02-06 17:36에 마지막으로 수정되었습니다.

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

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

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에 작성한 댓글입니다. 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에 작성한 댓글입니다.
이 댓글은 2018-02-07 07:54에 마지막으로 수정되었습니다.

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

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

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

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

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

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

[Top]

No.	제목	작성자	작성일	조회
31157	MySQL에서 테이블 JOIN 시 퍼포먼스가 궁금합니다.	김종수	2018-02-08	5305
31155	제가 메모장에 적어둔 SQL인데 기능을 까먹었습니다..이게 어떤건지좀 봐주세요 [1]	rladmsxor93	2018-02-07	4663
31154	[질문]컬럼의 합계를 구하는 쿼리를 만들고자 합니다. [1]	신승익	2018-02-06	4655
31153	다음 쿼리를 JOIN으로 해야 하나요? SELECT를 두번으로 해야 하나요? [7]	쿼리초보	2018-02-06	4847
31152	[질문]테이블 1에서 테이블2로 자료 넘겨주기 [3]	신승익	2018-01-29	4468
31151	like 문장 거꾸로 실행하는법 [4]	김양훈	2018-01-25	4877
31150	날짜와 시간을 토대로 select 하려면 어떻게 해야할까요 ? [1]	asdf	2018-01-24	4793