力扣题目跳转（1384. 按年度列出销售总额 - 力扣（LeetCode））
Product表：+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | product_name | varchar | +---------------+---------+ product_id 是这张表的主键(具有唯一值的列)。 product_name 是产品的名称。
Sales表：+---------------------+---------+ | Column Name | Type | +---------------------+---------+ | product_id | int | | period_start | date | | period_end | date | | average_daily_sales | int | +---------------------+---------+ product_id 是这张表的主键(具有唯一值的列)。 period_start 和 period_end 是该产品销售期的起始日期和结束日期，且这两个日期包含在销售期内。 average_daily_sales 列存储销售期内该产品的日平均销售额。 销售日期范围为2018年到2020年。
题目要求：
编写解决方案，找出每个产品每年的总销售额，并包含 product_id , product_name , report_year 以及 total_amount 。
返回结果并按 product_id 和 report_year 排序。
返回结果格式如下例所示。
示例 1：
输入： Product table: +------------+--------------+ | product_id | product_name | +------------+--------------+ | 1 | LC Phone | | 2 | LC T-Shirt | | 3 | LC Keychain | +------------+--------------+Sales table: +------------+--------------+-------------+---------------------+ | product_id | period_start | period_end | average_daily_sales | +------------+--------------+-------------+---------------------+ | 1 | 2019-01-25 | 2019-02-28 | 100 | | 2 | 2018-12-01 | 2020-01-01 | 10 | | 3 | 2019-12-01 | 2020-01-31 | 1 | +------------+--------------+-------------+---------------------+ 输出： +------------+--------------+-------------+--------------+ | product_id | product_name | report_year | total_amount | +------------+--------------+-------------+--------------+ | 1 | LC Phone | 2019 | 3500 | | 2 | LC T-Shirt | 2018 | 310 | | 2 | LC T-Shirt | 2019 | 3650 | | 2 | LC T-Shirt | 2020 | 10 | | 3 | LC Keychain | 2019 | 31 | | 3 | LC Keychain | 2020 | 31 | +------------+--------------+-------------+--------------+ 解释： LC Phone 在 2019-01-25 至 2019-02-28 期间销售，该产品销售时间总计35天。销售总额 35*100 = 3500。 LC T-shirt 在 2018-12-01 至 2020-01-01 期间销售，该产品在2018年、2019年、2020年的销售时间分别是31天、365天、1天，2018年、2019年、2020年的销售总额分别是31*10=310、365*10=3650、1*10=10。 LC Keychain 在 2019-12-01 至 2020-01-31 期间销售，该产品在2019年、2020年的销售时间分别是：31天、31天，2019年、2020年的销售总额分别是31*1=31、31*1=31。
case 1 的建表语句。
Create table If Not Exists Product (product_id int, product_name varchar(30))
Create table If Not Exists Sales (product_id int, period_start date, period_end date, average_daily_sales int)
Truncate table Product
insert into Product (product_id, product_name) values ('1', 'LC Phone ')
insert into Product (product_id, product_name) values ('2', 'LC T-Shirt')
insert into Product (product_id, product_name) values ('3', 'LC Keychain')
Truncate table Sales
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('1', '2019-01-25', '2019-02-28', '100')
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('2', '2018-12-01', '2020-01-01', '10')
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('3', '2019-12-01', '2020-01-31', '1')
一 首先我们通过遍历求出两个时间段中所有的年份，方便后面的使用。这是重点。
with recursive tmp as
(select product_id as pd, year(period_start) as s, year(period_end) as e from Sales
union
select
pd,
s + 1,
e
from tmp where s < e
)
使用 CTE 遍历，我们只需要 id 起始时间的年份和结束时间的年份，每次遍历起始年份加一，知道等于结束年份。
输出如下
二 在这个基础上，我们再通过左连接原来的 sales 表和 proudct 表。然后我们再查询我们需要用到的字段。
with recursive tmp as
(select product_id as pd, year(period_start) as s, year(period_end) as e from Sales
union
select
pd,
s + 1,
e
from tmp where s < e
)select
s,
e,
pd,
product_name,
period_start,
PERIOD_END,
average_daily_sales
from tmp t
left join SALES s on t.pd =s.product_id
left join PRODUCT p on t.pd = p.product_id;
输出如下
三 上面的所有目的都是求出这张表，拿到这张表基本上我们就做出来了。我们现在只需要求出每个时间段的天数，然后就可以相乘得出答案。那么怎么求天数呢，这里我们可以使用 case when 语句进行分类。这里解释下，以防大家看不懂。 s 代表的是当前年份，e代表的是最终年份，当 s = e and s = year(period_start) 表示当前年份和最终年份相等，并且也等于最初年份。即天数为最终日期减去起始日期，因为他们是在同一年。以此类推， s = year(period_start) and s < e 表示当前年份就是起始年份，但是不是最终年份，这一年的天数则为该年的最后一天减去起始日期。后面的思路类似我就不复述了。
with recursive tmp as
(select product_id as pd, year(period_start) as s, year(period_end) as e from Sales
union
select
pd,
s + 1,
e
from tmp where s < e
)
select s,
e,
pd,
product_name,
period_start,
PERIOD_END,
average_daily_sales,
case
when s = e and s = year(period_start) then datediff(period_end , period_start) + 1
when s = year(period_start) and s < e then datediff(concat(s,'-12-31'),period_start) + 1
when s > year(period_start) and s < e then datediff(concat(s,'-12-31'),concat(s,'-01-01')) + 1
when s > year(period_start) and s = e then datediff(period_end,concat(e,'-01-01')) + 1
end as days
from tmp t
left join SALES s on t.pd =s.product_id
left join PRODUCT p on t.pd = p.product_id
order by t.pd,s;
输出如下
四 那么答案呼之欲出了，我们只需要查询需要的列换个别名，然后将 days *average_daily_sales求出来即可。
with recursive tmp as
(select product_id as pd, year(period_start) as s, year(period_end) as e from Sales
union
select
pd,
s + 1,
e
from tmp where s < e
),tmp2 as (
select s,
e,
pd,
product_name,
period_start,
PERIOD_END,
average_daily_sales,
case
when s = e and s = year(period_start) then datediff(period_end , period_start) + 1
when s = year(period_start) and s < e then datediff(concat(s,'-12-31'),period_start) + 1
when s > year(period_start) and s < e then datediff(concat(s,'-12-31'),concat(s,'-01-01')) + 1
when s > year(period_start) and s = e then datediff(period_end,concat(e,'-01-01')) + 1
end as days
from tmp t
left join SALES s on t.pd =s.product_id
left join PRODUCT p on t.pd = p.product_id
order by t.pd,s)
select
pd as product_id,
product_name,
concat(s) as report_year,
days * average_daily_sales as total_amount
from tmp2;
这里别忘了将 s 的格式按照题目要求转成字符串。
输出如下
以上就是全部答案，如果对你有帮助请点个赞，谢谢。
来源：力扣（leecode）
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。
转载请注明出处:
我会尽快把力扣上的所有数据库题目发出来。感兴趣的可以点个赞与关注。每天不定时跟新。
标签：product,01,销售总额,period,列出,start,year,1384,id From： https://blog.csdn.net/CYJ1844/article/details/143083443