[TIL] 241118

sql 공부

#Lv3. 이용자의 포인트 조회하기
SELECT u.user_id,
	   u.email,
	   if(pu.point is null,0,point) point #(coalesce를 사용하면  if ~ is null 을 대체
FROM users u left join point_users pu on u.user_id = pu.user_id 
order by point desc
;

#Lv4. 단골 고객님 찾기
#1
select c.CustomerName ,
	   count(o.CustomerID) OrderCount,
	   sum(o.TotalAmount) TotalAmount
from Customer c left join Orders o on c.CustomerID = O.CustomerID 
group by c.CustomerName
;

#2 gpt형님짱
SELECT 
    c.Country,
    c.CustomerName AS Top_Customer,
    SUM(o.TotalAmount) AS Top_Spent
FROM 
    Customers c
JOIN 
    Orders o ON c.CustomerID = o.CustomerID
GROUP BY 
    c.Country, c.CustomerName
HAVING 
    SUM(o.TotalAmount) = (
        SELECT 
            MAX(SumSpent)
        FROM 
            (SELECT 
                 SUM(o2.TotalAmount) AS SumSpent
             FROM 
                 Customers c2
             JOIN 
                 Orders o2 ON c2.CustomerID = o2.CustomerID
             WHERE 
                 c2.Country = c.Country
             GROUP BY 
                 c2.CustomerID) AS Subquery
    );

서브쿼리와 having