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
