[MySQL] 서브쿼리로 JOIN 테이블 최댓값 레코드 가져오기

다음과 같은 Orders, Customers 테이블에서 나라별로 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회하는 SQL 쿼리를 작성하는 문제였다.

일단 다음과 같은 쿼리를 이용하여 CustomerName, Country 별로 총 주문금액을 뽑아냈다.

SELECT c.CustomerName, c.Country, SUM(o.TotalAmount) AS t
FROM Customers AS c
LEFT JOIN Orders AS o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, c.Country

여기서 두개의 USA 레코드 중 t가 최대인 레코드를 뽑아내는 것이 문제였다.

HAVING절을 사용해 문제를 해결하려고 했으나 t 값에 변동하는 조건을 주는 방법을 찾지 못했다.

SELECT c.CustomerName, c.Country, SUM(o.TotalAmount) as t
FROM Customers as c
LEFT JOIN Orders as o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, c.Country
HAVING t = (서브쿼리);

서브쿼리에서 c.Country 를 이용해 하나의 값만 반환하는 부분에서 막혔다.

그 과정에서 서브쿼리가 메인쿼리의 변수에 접근이 가능하다는 것을 발견하고, 다음과 같은 쿼리를 만들었다.

SELECT c.CustomerName, c.Country, SUM(o.TotalAmount) as t
FROM Customers as c
LEFT JOIN Orders as o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, c.Country
HAVING t = (
    SELECT MAX(SUM(o2.TotalAmount))
    FROM Orders as o2
    INNER JOIN Customers as c2
    ON o2.CustomerID = c2.CustomerID
    WHERE c2.Country = c.Country
    GROUP BY c2.Country
);

그러나 결과는 다음과 같았다.

알아보니, MySQL에서 집계함수(집계함수)의 형태는 사용이 불가능해 MAX(SUM(o2.TotalAmount)) 부분에서 에러가 발생하였다.

이를 해결하기 위해 서브쿼리 내부에서 서브쿼리를 사용하여 구한 SUM값 중 최댓값을 뽑아내기로 했다.

SELECT c.CustomerName, c.Country, SUM(o.TotalAmount) AS t
FROM Customers AS c
LEFT JOIN Orders AS o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, c.Country
HAVING t = (
    SELECT MAX(CustomerTotal.t2)
    FROM (
    // Customers, Orders 에서 
    // 총 주문금액(SUM(o2.TotalAmount) = t2)를 찾고 그 중 MAX 추출
        SELECT c2.Country, c2.CustomerID, SUM(o2.TotalAmount) AS t2
        FROM Customers AS c2
        LEFT JOIN Orders AS o2
        ON c2.CustomerID = o2.CustomerID
        GROUP BY c2.CustomerID, c2.Country
    ) AS CustomerTotal	
    // 메인쿼리의 c.Country 참조
    WHERE CustomerTotal.Country = c.Country
);

결과

SELECT c.CustomerName, c.Country, SUM(o.TotalAmount) AS t
FROM Customers AS c
LEFT JOIN Orders AS o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, c.Country
HAVING t = (
    SELECT MAX(CustomerTotal.t2)
    FROM (
        SELECT c2.Country, c2.CustomerID, SUM(o2.TotalAmount) AS t2
        FROM Customers AS c2
        LEFT JOIN Orders AS o2
        ON c2.CustomerID = o2.CustomerID
        GROUP BY c2.CustomerID, c2.Country
    ) AS CustomerTotal
    WHERE CustomerTotal.Country = c.Country
);

나라별 총 주문금액이 높은 고객 레코드 받기 성공

정리

1. 서브쿼리에서 메인쿼리의 변수에 접근이 가능하다.
2. 집계함수는 중첩으로 사용할 수 없다.