关于后端:LeetCode-mysql-刷题六指定日期的产品价格3种方法处理未查询到日期

题目

题目链接：指定日期的产品价格

编写一个解决方案，找出在 2019-08-16 时全副产品的价格，假如所有产品在批改前的价格都是 10。

以 任意程序 返回后果表。

后果格局如下例所示。

 输出：Products 表:
+------------+-----------+-------------+
| product_id | new_price | change_date |
+------------+-----------+-------------+
| 1          | 20        | 2019-08-14  |
| 2          | 50        | 2019-08-14  |
| 1          | 30        | 2019-08-15  |
| 1          | 35        | 2019-08-16  |
| 2          | 65        | 2019-08-17  |
| 3          | 20        | 2019-08-18  |
+------------+-----------+-------------+
(product_id, change_date) 是此表的主键（具备惟一值的列组合）。这张表的每一行别离记录了 某产品 在某个日期 更改后 的新价格。输入：+------------+-------+
| product_id | price |
+------------+-------+
| 2          | 50    |
| 1          | 35    |
| 3          | 10    |
+------------+-------+

Create table If Not Exists Products (product_id int, new_price int, change_date date);
Truncate table Products;
insert into Products (product_id, new_price, change_date) values ('1', '20', '2019-08-14');
insert into Products (product_id, new_price, change_date) values ('2', '50', '2019-08-14');
insert into Products (product_id, new_price, change_date) values ('1', '30', '2019-08-15');
insert into Products (product_id, new_price, change_date) values ('1', '35', '2019-08-16');
insert into Products (product_id, new_price, change_date) values ('2', '65', '2019-08-17');
insert into Products (product_id, new_price, change_date) values ('3', '20', '2019-08-18');

解析

这题考查的点是如何查出 product_id 为 3 的数据，因为这个产品在 2019-08-16 之前没有批改过价格

办法一

思路：

查问出在 2019-08-16 之前的最新批改过价格的 product_id，

如果批改过价，那么 new_price 是有值的

没有批改过价格，那么 new_price 是 null，给它赋值为 10

+------------+-------------+
| product_id | new_price   |
+------------+-------------+
| 1          | 35          |
| 2          | 50          |
| 3          | NULL        |
+------------+-------------+

步骤：

1. 依照 product_id，查问出 2019-08-16 日期之前的最新批改过价格的产品，留神： 这里查问不出价格
2. 应用子查问，依据 product_id 和 change_date 查问出 product_id 和 new_price，作为长期表 tmp2

3. 查问出所有产品的 product_id，作为长期表 tmp1

   • SELECT product_id FROM Products GROUP BY product_id
   • SELECT DISTINCT product_id FROM Products

4. 左连查问，将 tmp1 和 tmp2 左连，连贯条件是 product_id

   • 应用 if 或者 ifnull 判断 new_price 是否为空，如果为空，则应用 10 作为价格

SELECT
   product_id, IF(new_price, new_price, 10) price
FROM (SELECT product_id FROM Products GROUP BY product_id) tmp1
LEFT JOIN (SELECT product_id, new_price FROM Products WHERE ( product_id, change_date)
   IN (SELECT product_id, max(change_date) FROM Products WHERE DATE (change_date) <= DATE ("2019-08-16") GROUP BY product_id )
) tmp2 USING (product_id)

办法二

办法二的思路正好和办法一是相同的

思路：

查问出每个产品在 2019-08-16 之前，最初批改价格的日期，在左连 Products 表，那么 product_id 为 3 的产品就没有价格

+------------+-------------+
| product_id | change_date |
+------------+-------------+
| 1          | 2019-08-16  |
| 2          | 2019-08-14  |
| 3          | NULL        |
+------------+-------------+

步骤：

1. 依照 product_id 分组，查问出 2019-08-16 之前，最初批改价格的日期，作为长期表 tmp

2. 将 tmp 和 Products 左连，连贯条件是 product_id 和 change_date

   • 应用 if 或者 ifnull 判断 new_price 是否为空，如果为空，则应用 10 作为价格

WITH tmp AS (
    SELECT
      product_id, MAX(IF(DATE ( change_date) <= DATE ("2019-08-16"), change_date, NULL)) change_date
   FROM Products GROUP BY product_id
)
SELECT
   product_id, IF(new_price, new_price, 10) price
FROM tmp LEFT JOIN Products USING(product_id, change_date)

办法三

思路：

应用窗口函数，将每个产品依照批改日期进行排序

步骤：

1. 查问出在 2019-08-16 日期前批改过价格的记录，将大于 2019-08-16 的日期设置为 null，作为长期表 tmp
2. 应用窗口函数，依照 product_id 分组，依照 change_date 降序排序，作为长期表 tmp2

3. 查问出 tmp2，筛选出 rk = 1 的 product_id 和 new_price

   • 应用 if 或者 ifnull 判断 new_price 是否为空，如果为空，则应用 10 作为价格
   • 应用 distinct 去重，可能会有某个产品某天批改屡次的记录

SELECT
   DISTINCT product_id, IF(change_date, new_price, 10) price
FROM (
   SELECT
      *, RANK() OVER(PARTITION BY product_id ORDER BY change_date DESC) AS rk
   FROM (SELECT product_id, new_price, IF(DATE( change_date) > DATE("2019-08-16"), NULL, change_date) change_date FROM Products
   ) tmp
) tmp2 WHERE rk = 1

相关联的题目

按分类统计薪水

往期 MySQL 题目

1. MySQL 题目
2. LeetCode mysql 刷题一：计算非凡奖金 | 买下所有产品的客户
3. LeetCode mysql 刷题二：电影评分——判断日期的五种办法
4. LeetCode mysql 刷题三：确认率——MySQL 中的 null 解决 | 判断三角形的四种办法
5. LeetCode mysql 刷题四：餐馆营业额变动增长——用自连和窗口函数 4 种 sql 实现过来 7 天的营业额
6. LeetCode mysql 刷题五：按分类统计薪水——用 5 种办法解决不存在的数据但要显示的数据