题目链接：点击打开链接
题目大意：略。
解题思路：略。
AC 代码
WITHt0AS(SELECT*, DATE_FORMAT(period_start, '%Y') fromY, DATE_FORMAT(period_end, '%Y') toYFROMSalesWHEREDATE_FORMAT(period_start, '%Y') =DATE_FORMAT(period_end, '%Y')), t1AS(SELECT*, DATE_FORMAT(period_start, '%Y') fromY, DATE_FORMAT(period_end, '%Y') toYFROMSalesWHEREDATE_FORMAT(period_start, '%Y') -DATE_FORMAT(period_end, '%Y') =-1), t2AS(SELECT*, DATE_FORMAT(period_start, '%Y') fromY, DATE_FORMAT(period_end, '%Y') toYFROMSalesWHEREDATE_FORMAT(period_start, '%Y') -DATE_FORMAT(period_end, '%Y') =-2), t3AS(SELECTproduct_id, fromYreport_year, (DATEDIFF(period_end, period_start) +1) *average_daily_salestotal_amountFROMt0UNIONALLSELECTproduct_id, fromY, (DATEDIFF(CONCAT(fromY, '-12-31'), period_start) +1) *average_daily_salestotal_amountFROMt1UNIONALLSELECTproduct_id, toY, (DATEDIFF(period_end, CONCAT(toY, '-01-01')) +1) *average_daily_salestotal_amountFROMt1UNIONALLSELECTproduct_id, fromY, (DATEDIFF(CONCAT(fromY, '-12-31'), period_start) +1) *average_daily_salestotal_amountFROMt2UNIONALLSELECTproduct_id, fromY+1, 365*average_daily_salestotal_amountFROMt2UNIONALLSELECTproduct_id, toY, (DATEDIFF(period_end, CONCAT(toY, '-01-01')) +1) *average_daily_salestotal_amountFROMt2) SELECTCONCAT('', p.product_id) product_id, product_name, report_year, total_amountFROMt3JOINProductpONp.product_id=t3.product_idORDERBYproduct_id, report_year
