sql面试题目

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小时的时间。