Leetcode1384.按年度列出销售总额（困难）

这道题确实值得困难的难度
也很有意思
值得慢慢来做

问题
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.

生成数据

Create table If Not Exists Product (product_id int, product_name varchar(30))
Create table If Not Exists Sales (product_id varchar(30), 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')

解答
不太会
看着别人的答案摸索一下

先构造一个集合每一年的年份和对应起始日期和结束日期

SELECT 
  '2018' AS report_year,
  DATE('2018-01-01') AS start_date,
  DATE('2018-12-31') AS end_date 
FROM
  DUAL 
UNION
ALL 
SELECT 
  '2019' AS YEAR,
  DATE('2019-01-01') AS start_date,
  DATE('2019-12-31') AS end_date 
FROM
  DUAL 
UNION
ALL 
SELECT 
  '2020' AS YEAR,
  DATE('2020-01-01') AS start_date,
  DATE('2020-12-31') AS end_date 
FROM
  DUAL

from dual 是一个虚拟表纯粹是为了满足select … from…这一习惯问题，mysql会忽略对该表的引用。

有三种情况

销售区间包含自然年的前半段
这种情况下是period_end与start_date的差值加一就是所需天数
销售区间包含自然年的后半段
这种情况下 end_date与period_start的差值加一就是需要的天数
销售区间包含自然年的全部
这时period_end与period_start的差值+1即可

以上表与sales表按以上三种情况进行连接

SELECT *
FROM Sales1 AS S, (SELECT '2018'             AS report_year,
                DATE('2018-01-01') AS start_date,
                DATE('2018-12-31') AS end_date
         FROM DUAL
         UNION ALL
         SELECT '2019'             AS YEAR,
                DATE('2019-01-01') AS start_date,
                DATE('2019-12-31') AS end_date
         FROM DUAL
         UNION ALL
         SELECT '2020'             AS YEAR,
                DATE('2020-01-01') AS start_date,
                DATE('2020-12-31') AS end_date
         FROM DUAL) AS t
WHERE   # 销售区间包含自然年的后半段
    S.`period_start` BETWEEN t.start_date AND t.end_date 
    # 销售区间包含自然年的前半段
    OR  S.`period_end` BETWEEN t.start_date AND t.end_date 
    # 整个自然年都在销售区间之内
    OR  (S.`period_start`>= t.start_date AND S.`period_end` <= t.end_date);

我开始的想法是根据三种分类分别求出三种的天数差
但是是行不通的
首先忽略了这种情况比如20190401- 2019-0501 满足销售区间包含自然年的后半段也满足销售区间包含自然年的前半段
但是其计算方式只要period_end与period_start做时间差即可

其实发现上边是有"规律"的
1.都是end与start 求datediff
2.end取得是两个end中最小的 start则取得是两个start中的最大的
时间差记得加1

SELECT S.product_id, t.report_year,
(DATEDIFF(IF(S.`period_end`< t.end_date, S.`period_end`,t.end_date),
IF(S.`period_start` < t.start_date, t.start_date, S.`period_start`))+ 1) *S.`average_daily_sales` AS total_amount
FROM Sales1 AS S, (SELECT '2018'             AS report_year,
                DATE('2018-01-01') AS start_date,
                DATE('2018-12-31') AS end_date
         FROM DUAL
         UNION ALL
         SELECT '2019'             AS YEAR,
                DATE('2019-01-01') AS start_date,
                DATE('2019-12-31') AS end_date
         FROM DUAL
         UNION ALL
         SELECT '2020'             AS YEAR,
                DATE('2020-01-01') AS start_date,
                DATE('2020-12-31') AS end_date
         FROM DUAL) AS t
WHERE   # 销售区间包含自然年的后半段
    S.`period_start` BETWEEN t.start_date AND t.end_date 
    # 销售区间包含自然年的前半段
    OR  S.`period_end` BETWEEN t.start_date AND t.end_date 
    # 整个自然年都在销售区间之内
    OR  (t.start_date > S.`period_start` AND t.end_date < S.`period_end`);

最后当然是简单的两表连接啦

SELECT P.product_id, P.`product_name`, tmp.report_year, tmp.total_amount
FROM (
SELECT S.product_id, t.report_year,
(DATEDIFF(IF(S.`period_end`< t.end_date, S.`period_end`,t.end_date),
IF(S.`period_start` < t.start_date, t.start_date, S.`period_start`))+ 1) *S.`average_daily_sales` AS total_amount
FROM Sales1 AS S, (SELECT '2018'             AS report_year,
                DATE('2018-01-01') AS start_date,
                DATE('2018-12-31') AS end_date
         FROM DUAL
         UNION ALL
         SELECT '2019'             AS YEAR,
                DATE('2019-01-01') AS start_date,
                DATE('2019-12-31') AS end_date
         FROM DUAL
         UNION ALL
         SELECT '2020'             AS YEAR,
                DATE('2020-01-01') AS start_date,
                DATE('2020-12-31') AS end_date
         FROM DUAL) AS t
WHERE   # 销售区间包含自然年的后半段
    S.`period_start` BETWEEN t.start_date AND t.end_date 
    # 销售区间包含自然年的前半段
    OR  S.`period_end` BETWEEN t.start_date AND t.end_date 
    # 整个自然年都在销售区间之内
    OR  (t.start_date > S.`period_start` AND t.end_date < S.`period_end`)
) AS tmp
JOIN Product3 AS P
ON P.product_id = tmp.product_id
ORDER BY P.product_id, P.`product_name`;