아래 ERD를 살펴보자. Shop과 Ord는 1:M 관계다. 그리고 Ord와 OrdDet도 1:M이다. 이 세 테이블의 관계를 간략하게 (1:M):M 이라고 말할 수 있다. 정확히는 앞쪽의 1:M 조인 결과가 나머지 M과 조인시에 1 집합이 된다. 복잡하니 간단하게 (1:M):M 조인라고 하도록 하자.
위와 같이 1:M:M 조인은 M:1:M 을 조인할 때 처럼 주의가 필요하지는 않다. 1부터 시작해서 조인 컬럼만 잘 생각해서 조인 처리하면 된다. 하지만 조인 후 GROUP BY 할 때는 주의가 필요하다.
아래와 같은 결과를 얻으려고 한다. 매장시작일이 2018년 3월인 매장의 2022년 1월의 주문건수와 주문수량 합을 구하려고 한다. 여기서 주문 건수는 주문 테이블의 데이터를 카운트해야 한다. 그리고 주문수량의 합은 주문상세의 주문수량(OrdQty)을 SUM 처리해야 한다.
-- (1:M):M을 조인해 원하는 결과
ShopOperTp SUM_OrdCnt SUM_OrdQty
---------- ---------- ----------
DIST 20 27
DRCT 6 9
FLAG 135 182
위 결과를 보면 주문건수와 주문수량이 다르다는 점에 주의가 필요하다. 하나의 주문에 여러 상품을 여러 개 주문할 수 있기 때문에 주문수량은 주문 건수보다 더 클 수 있다.
위와 같은 결과를 얻기 위해 우선 Shop과 Ord, OrdDet를 조인해보자. 다음과 같다.
-- [SQL-9-5-1-a]
SELECT T1.ShopId ,T1.ShopNm ,T1.ShopOperTp
,T2.OrdNo ,T2.OrdDtm
,T3.OrdDetNo ,T3.ItemId ,T3.OrdQty
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
INNER JOIN startdb.OrdDet T3
ON (T3.OrdNo = T2.OrdNo)
WHERE T1.ShopStartYmd LIKE '201803%'
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
ORDER BY T1.ShopId ,T2.OrdNo ,T3.OrdDetNo;
ShopId ShopNm ShopOperTp OrdNo OrdDtm OrdDetNo ItemId OrdQty
------ ------------- ---------- ----- ------------------- -------- ------ ------
S008 San Diego-1st FLAG 25286 2022-01-02 09:30:00 1 BGLR 1
S008 San Diego-1st FLAG 25445 2022-01-05 11:30:00 1 HCHB 1
S008 San Diego-1st FLAG 25456 2022-01-05 11:30:00 1 CLB 1
S008 San Diego-1st FLAG 25504 2022-01-05 12:30:00 1 IAMR 1
S008 San Diego-1st FLAG 25612 2022-01-11 11:30:00 1 CMFR 1
-- > S008 San Diego-1st FLAG 25623 2022-01-11 11:30:00 1 BMFR 1
-- > S008 San Diego-1st FLAG 25623 2022-01-11 11:30:00 2 ICLB 1
S008 San Diego-1st FLAG 25671 2022-01-11 12:30:00 1 IAMB 1
S008 San Diego-1st FLAG 25797 2022-01-23 11:30:00 1 IAMB 1
... 생략 ...
위 SQL의 조인은 정상이다. 다만, 위 SQL의 결과에 ‘-- >’로 표시한 부분처럼 하나의 주문에 여러 건의 주문 상세가 있을 수 있다는 점을 주의해야 한다.
이제 위 결과를 ShopOperTp별로 GROUP BY 처리해 주문건수와 주문수량 합계를 구해보자. 다음과 같이 SQL을 작성해볼 수 있다.
-- [SQL-9-5-1-b]
SELECT T1.ShopOperTp
,COUNT(T2.OrdNo) SUM_OrdCnt
,SUM(T3.OrdQty) SUM_OrdQty
FROM startdb.Shop T1
INNER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId)
INNER JOIN startdb.OrdDet T3
ON (T3.OrdNo = T2.OrdNo)
WHERE T1.ShopStartYmd LIKE '201803%'
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
GROUP BY T1.ShopOperTp
ORDER BY T1.ShopOperTp;
ShopOperTp SUM_OrdCnt SUM_OrdQty
---------- ---------- ----------
DIST 27 27
DRCT 9 9
FLAG 182 182
이제 SQL로 결과를 얻었으니, 보고서에 수치를 옮겨 적고 신나게 보고해보자. 진짜 이대로 보고 할 것인가? 잘 살펴보기 바란다. 위 수치는 문제가 있다. 주문건수와 주문수량 합계의 값이 같다. 조인 과정에서 Ord의 레코드가 OrdDet만큼 늘어났기 때문에, 주문건수를 단순 COUNT로 처리하면 이와 같은 문제가 발생된다.