1384. Total Sales Amount by Year----取日期中的年份year()和计算日期之间的差别datediff()

Table: Product

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| product_name  | varchar |
+---------------+---------+
product_id is the primary key for this table.
product_name is the name of the product.

 

Table: Sales

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| product_id          | int     |
| period_start        | varchar |
| period_end          | date    |
| average_daily_sales | int     |
+---------------------+---------+
product_id is the primary key for this table. 
period_start and period_end indicates the start and end date for sales period, both dates are inclusive.
The average_daily_sales column holds the average daily sales amount of the items for the period.

Write an SQL query to report the Total sales amount of each item for each year, with corresponding product name, product_id, product_name and report_year.

Dates of the sales years are between 2018 to 2020. Return the result table ordered by product_id and report_year.

The query result format is in the following example:

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                   |
+------------+--------------+-------------+---------------------+

Result table:
+------------+--------------+-------------+--------------+
| 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 was sold for the period of 2019-01-25 to 2019-02-28, and there are 35 days for this period. Total amount 35*100 = 3500. 
LC T-shirt was sold for the period of 2018-12-01 to 2020-01-01, and there are 31, 365, 1 days for years 2018, 2019 and 2020 respectively.
LC Keychain was sold for the period of 2019-12-01 to 2020-01-31, and there are 31, 31 days for years 2019 and 2020 respectively.

如果把s.product_id换成p.product_id, leetcode就会报错， why？

另外，时间格式链接：https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html

WITH TMP AS (SELECT s.product_id, p.product_name, '2018' AS report_year,
CASE WHEN s.period_end <= '2018-12-31'
     THEN  (DATEDIFF(s.period_end,s.period_start) + 1) * average_daily_sales 
     WHEN s.period_end > '2018-12-31'
     THEN (DATEDIFF('2018-12-31', s.period_start) + 1) * average_daily_sales 
     END AS total_amount
FROM product p, sales s
WHERE p.product_id = s.product_id
AND YEAR(s.period_start) = '2018'
UNION
SELECT s.product_id, p.product_name, '2019' AS report_year,
CASE WHEN YEAR(s.period_end) ='2019' AND YEAR(s.period_start) = '2019'
     THEN (DATEDIFF(s.period_end,s.period_start) + 1) * average_daily_sales 
     WHEN YEAR(s.period_end) ='2019' AND YEAR(s.period_start) = '2018'
     THEN (DATEDIFF(s.period_end, '2019-01-01') + 1) * average_daily_sales 
     WHEN YEAR(s.period_end) ='2020' AND YEAR(s.period_start) = '2019'
     THEN (DATEDIFF('2019-12-31', s.period_start) + 1) * average_daily_sales
     WHEN YEAR(s.period_end) ='2020' AND YEAR(s.period_start) = '2018' 
     THEN 365 * average_daily_sales
     END AS total_amount
FROM product p, sales s
WHERE p.product_id = s.product_id
AND YEAR(period_start)<2020 AND YEAR(period_end) >2018
UNION 
SELECT s.product_id, p.product_name, '2020' AS report_year,
CASE WHEN YEAR(s.period_start) = '2020'
     THEN (DATEDIFF(s.period_end, s.period_start) + 1) * average_daily_sales 
     WHEN YEAR(s.period_start) < '2020'
     THEN (DATEDIFF(s.period_end,'2020-01-01') + 1) * average_daily_sales 
     END AS total_amount
FROM product p, sales s
WHERE p.product_id = s.product_id
AND YEAR(s.period_end) = '2020')
SELECT *
FROM TMP 
ORDER BY PRODUCT_ID,REPORT_YEAR;