1384.png

https://leetcode-cn.com/problems/total-sales-amount-by-year/

select a.product_id,b.product_name,a.report_year,a.y1 total_amount
from(
select product_id
,"2018" report_year
,average_daily_sales*(case when year(period_start)=2018 then 
   case when year(period_end)=2018 then datediff(period_end,period_start)+1 
        when year(period_end)>2018 then datediff('2018-12-31',period_start)+1 
    end
else 0 end) y1
from Sales
    union all 
select product_id
,"2019" report_year
,average_daily_sales*(case when year(period_start)=2018 then 
        case when year(period_end)=2019 then datediff(period_end,'2019-01-01')+1 
             when year(period_end)>2019 then 365 end
      when year(period_start)=2019 then 
        case when year(period_end)=2019 then datediff(period_end,period_start)+1 
             when year(period_end)>2019 then datediff('2019-12-31',period_start)+1 end
      else 0 end) y1
from Sales
    union all
select product_id
,"2020" report_year
,average_daily_sales*(case when year(period_start)<2020 then 
        case when year(period_end)=2020 then datediff(period_end,'2020-01-01')+1 end 
      when year(period_start)=2020 then 
        case when year(period_end)=2020 then datediff(period_end,period_start)+1 end
      else 0 end) y1
from Sales
)a left join Product b on a.product_id=b.product_id
where a.y1>0 
order by a.product_id,report_year