MySQL基础练习题13-指定日期的产品价格

题目：找出在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。

准备数据

分析数据

---

题目：找出在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。

准备数据

## 创建库
create tadabase db;
use db;

## 创建表
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', '5', '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');

输入表

 分析数据

遇到这种分组后而找其中一行，可以用开窗函数进行排序。

 第一步：

## 第一步：利用开窗函数将日期在2019-08-16之前的排序
select product_id,
                   new_price,
                   change_date,
                   row_number() over (partition by product_id order by change_date desc) num
            from Products
            where change_date <= '2019-08-16';

第二步：

## 第二步：将第一步的开窗作为一个临时表，选出最新的价格
select product_id, new_price, change_date
      from (select product_id,
                   new_price,
                   change_date,
                   row_number() over (partition by product_id order by change_date desc) num
            from Products
            where change_date <= '2019-08-16') t
      where t.num = 1) t1;

 第三步：

## 第三步：将原表左连接t1表，此时就会出现id=3的列，但是price是null。再利用ifnull()函数将null替换成10
select distinct p1.product_id, ifnull(t1.new_price, 10) as price
from Products p1
         left join
     (select product_id, new_price, change_date
      from (select product_id,
                   new_price,
                   change_date,
                   row_number() over (partition by product_id order by change_date desc) num
            from Products
            where change_date <= '2019-08-16') t
      where t.num = 1) t1
     on p1.product_id = t1.product_id;

 注意：

ifnull（）可以替换成coalesce（）

• COALESCE(value1, value2, ..., valueN)：返回这些参数的第一个非空值。
• IFNULL(expression_1,expression_2)：只有两个参数，不是null返回第一个参数，如果是null，则返回第二个参数。