sql逻辑：
create table ods.product(product_id int,product_name varchar(255))ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
insert into ods.product values(1,'LC Phone');
insert into ods.product values(2,'LC T-Shirt');
select * from ods.product;
create table ods.sales(product_id int,product_start date,product_end date,average_daily_sales int)ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
insert into ods.sales values(1,'2019-01-25','2019-02-20',100);
insert into ods.sales values(2,'2018-12-01','2020-01-01',10);
insert into ods.sales values(3,'2019-12-01','2020-01-31',1);
select a.* from
(select product_id,case when(year(product_start)=year(product_end) ) then year(product_start)
when(year(product_end) =year(product_start)+1 ) then year(product_start)
when(year(product_end) =year(product_start)+2) then year(product_start) end as report_year,case when(year(product_end) =year(product_start)+1 ) or (year(product_end) =year(product_start)+2) then (TO_DAYS(CONCAT(year(product_start),'-12-31'))-TO_DAYS(product_start)+1)*average_daily_sales
when(year(product_start)=year(product_end) ) then ((TO_DAYS(product_end)-TO_DAYS(product_start)+1)*average_daily_sales)
end as total_amount from ods.sales
union all
select product_id,case when(year(product_end)=year(product_start)+1 ) then year(product_start)+1
when(year(product_end) =year(product_start)+2) then year(product_start)+2 end as report_year,case when(year(product_end)=year(product_start)+1 ) or (year(product_end) =year(product_start)+2) then DAYOFYEAR(product_end)*average_daily_sales end as total_amount from ods.sales where
year(product_end) != year(product_start)
union all
select product_id,case when(year(product_end) =year(product_start)+2) then year(product_start)+1 end as report_year, case when(year(product_end) =year(product_start)+2) then 365*average_daily_sales end as total_amount from ods.sales where year(product_end) >=year(product_start)+2)a group by a.product_id,a.report_year,a.total_amount order by a.product_id ;
这道题在当时面试时，我没有做出来，花了将近1小时的时间。
sql (
SELECT Sales.product_id
,product_name
,'2018' AS 'report_year'
,if(period_start < '2019-01-01',(datediff(if(period_end < '2019-01-01',period_end,date('2018-12-31')),if(period_start >= '2018-01-01',period_start,date('2018-01-01')))+1)*average_daily_sales,0) AS total_amount
FROM Sales
JOIN Product
ON Sales.product_id = Product.product_id
HAVING total_amount > 0 ) union(
SELECT Sales.product_id
,product_name
,'2019' AS 'report_year'
,if( period_start < '2020-01-01',(datediff(if(period_end < '2020-01-01',period_end,date('2019-12-31')),if(period_start >= '2019-01-01',period_start,date('2019-01-01')))+1)*average_daily_sales ,0) AS total_amount
FROM Sales
JOIN Product
ON (Sales.product_id = Product.product_id )
HAVING total_amount > 0 ) union(
SELECT Sales.product_id
,product_name
,'2020' AS 'report_year'
,(datediff(if(period_end < '2021-01-01',period_end,date('2020-12-31')),if(period_start >= '2020-01-01',period_start,date('2020-01-01')))+1)*average_daily_sales AS total_amount
FROM Sales
JOIN Product
ON (Sales.product_id = Product.product_id)
HAVING total_amount > 0 )
ORDER BY product_id, report_year
