LeetCode 【困难】数据库-第1384 ：按照年度列出销售总额（每个产品、每年、总销额）

题目

dayofyear （date） :会得出该日期距离该年份最早那一天的天数。

dayofmonth（date） :会得出该日期距离本份最早那一天的天数。

dayofweek (date) :返回日期的工作日索引值，即星期日为1，星期一为2，星期六为7。

数据

结果

解答

1.先设计一张表---需要统计的年份及各年份的天数

select '2018' year, 365 days_of_year
    union all 
select '2019' year, 365 days_of_year
    union all 
select '2020' year, 366 days_of_year

2.计算总额

s.average_daily_sales 
* 
(
if(year(s.period_end) > y.year, y.days_of_year, dayofyear(s.period_end)) 
- 
if(year(s.period_start) < y.year, 1, dayofyear(s.period_start)) 
+1
) results

3. 连接两表、计算天数和总金额。

select  
	*,
	(if(year(s.period_end) > y.year, y.days_of_year, dayofyear(s.period_end)) - if(year(s.period_start) < y.year, 1, dayofyear(s.period_start)) + 1) 1_total_amount,
	if(year(s.period_end) > y.year, y.days_of_year, dayofyear(s.period_end)) 2_ends,
	if(year(s.period_start) < y.year, 1, dayofyear(s.period_start))  3_starts,
	s.average_daily_sales * (if(year(s.period_end) > y.year, y.days_of_year, dayofyear(s.period_end)) - if(year(s.period_start) < y.year, 1, dayofyear(s.period_start)) +1)  4_results			
from Sales s
inner join
(
    select '2018' year, 365 days_of_year
    union all select '2019' year, 365 days_of_year
    union all select '2020' year, 366 days_of_year
) y

4.连接条件

select  
	*,
	(if(year(s.period_end) > y.year, y.days_of_year, dayofyear(s.period_end)) - if(year(s.period_start) < y.year, 1, dayofyear(s.period_start)) + 1) 1_total_amount,
	if(year(s.period_end) > y.year, y.days_of_year, dayofyear(s.period_end)) 2_ends,
	if(year(s.period_start) < y.year, 1, dayofyear(s.period_start))  3_starts,
	s.average_daily_sales * (if(year(s.period_end) > y.year, y.days_of_year, dayofyear(s.period_end)) - if(year(s.period_start) < y.year, 1, dayofyear(s.period_start)) +1)  4_results			
from Sales s
inner join
(
    select '2018' year, 365 days_of_year
    union all select '2019' year, 365 days_of_year
    union all select '2020' year, 366 days_of_year
) y
on year(s.period_start) <= y.year and year(s.period_end) >= y.year

5.关联产品表，匹配产品名。排序

select  
	s.product_id,  #
	p.product_name,  #
	y.year report_year, 
	s.average_daily_sales * (if(year(s.period_end) > y.year, y.days_of_year, dayofyear(s.period_end)) - if(year(s.period_start) < y.year, 1, dayofyear(s.period_start)) +1)  4_results			
from Sales s
inner join
(
    select '2018' year, 365 days_of_year
    union all select '2019' year, 365 days_of_year
    union all select '2020' year, 366 days_of_year
) y
on year(s.period_start) <= y.year and year(s.period_end) >= y.year
inner join Product p
on p.product_id = s.product_id
order by s.product_id, y.year;