题目
题目链接：指定日期的产品价格
编写一个解决方案，找出在 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 |+------------+-------------+步骤：
- 依照
product_id，查问出2019-08-16日期之前的最新批改过价格的产品，留神：这里查问不出价格 - 应用子查问，依据
product_id和change_date查问出product_id和new_price，作为长期表tmp2 查问出所有产品的
product_id，作为长期表tmp1SELECT product_id FROM Products GROUP BY product_idSELECT DISTINCT product_id FROM Products
左连查问，将
tmp1和tmp2左连，连贯条件是product_id- 应用
if或者ifnull判断new_price是否为空，如果为空，则应用10作为价格
- 应用
SELECT product_id, IF(new_price, new_price, 10) priceFROM ( SELECT product_id FROM Products GROUP BY product_id) tmp1LEFT 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 |+------------+-------------+步骤：
- 依照
product_id分组，查问出2019-08-16之前，最初批改价格的日期，作为长期表tmp 将
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) priceFROM tmp LEFT JOIN Products USING(product_id, change_date)办法三
思路：
应用窗口函数，将每个产品依照批改日期进行排序
步骤：
- 查问出在
2019-08-16日期前批改过价格的记录，将大于2019-08-16的日期设置为null，作为长期表tmp - 应用窗口函数，依照
product_id分组，依照change_date降序排序，作为长期表tmp2 查问出
tmp2，筛选出rk = 1的product_id和new_price- 应用
if或者ifnull判断new_price是否为空，如果为空，则应用10作为价格 - 应用
distinct去重，可能会有某个产品某天批改屡次的记录
- 应用
SELECT DISTINCT product_id, IF(change_date, new_price, 10) priceFROM ( 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 题目
- MySQL 题目
- LeetCode mysql 刷题一：计算非凡奖金 | 买下所有产品的客户
- LeetCode mysql 刷题二：电影评分——判断日期的五种办法
- LeetCode mysql 刷题三：确认率——MySQL 中的 null 解决 | 判断三角形的四种办法
- LeetCode mysql 刷题四：餐馆营业额变动增长——用自连和窗口函数 4 种 sql 实现过来 7 天的营业额
- LeetCode mysql 刷题五：按分类统计薪水——用5种办法解决不存在的数据但要显示的数据