[MySQL] (Medium) Highest Energy Consumption

문제: https://platform.stratascratch.com/coding/10064-highest-energy-consumption?code_type=3

나의풀이:

with tb as (
    select date
         , ifnull(e.consumption, 0) as eu_con
         , ifnull(a.consumption, 0) as a_con
         , ifnull(n.consumption, 0) as n_con
    from fb_eu_energy e
         left join fb_asia_energy a using (date)
         left join fb_na_energy n using (date)
    union
    select date
         , ifnull(e.consumption, 0) as eu_con
         , ifnull(a.consumption, 0) as a_con
         , ifnull(n.consumption, 0) as n_con
    from fb_eu_energy e
         right join fb_asia_energy a using (date)
         right join fb_na_energy n using (date))
, sub as (
    select date
         , eu_con + a_con + n_con as consumption
    from tb)

select *
from sub
where consumption = (
    select max(consumption)
    from sub)

마이에스큐일은 full outer join을 지원하지 않는다고 한다! 그래서 left랑 right outer join 두개를 union 해줘야 한다고 함

그리구 합을 구할 거라서 null은 0처리를 해주었당

 

솔루션:

with ln as  (
select date,sum(consumption) as consumption 
from (
    select date,consumption from fb_eu_energy as na
    union
    select date,consumption from fb_asia_energy as asia
    union 
    select date,consumption from fb_na_energy as na
    ) as Data
group by date
) 

select * from ln
where consumption=(select Max(consumption) from ln)

ㅋ... 근데 솔루션보니까 나 진짜 바보같이 풀었네 ㅋㅋㅋㅋㅋㅋㅋ

그냥 세로로 다 합치고 날짜별로 그룹바이 해서 합을 구하면 된다... 조인이나 널 처리 할 것도 없음 멌쓱