指定日期的产品价格

题目 #

1164. 指定日期的产品价格 #

产品数据表: Products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+
(product_id, change_date) 是此表的主键（具有唯一值的列组合）。
这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。

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

以 任意顺序 返回结果表。

结果格式如下例所示。

示例 1:

输入：
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 | price |
+------------+-------+
| 2          | 50    |
| 1          | 35    |
| 3          | 10    |
+------------+-------+

解答 #

使用子查询+ifnull #

  1. 首先查出全部的product_id信息
  2. 查询在2019-08-16之前出现过的最大日期信息
  3. 获取新的价格信息
  4. 和全部product_id信息做left join
select p1.product_id,ifnull(p2.new_price,10) as price
from (
    select distinct product_id
    from products
) p1
left join(
    select product_id,new_price
    from products
    where (product_id,change_date) in (
        select product_id,max(change_date)
        from  products
        where change_date<='2019-08-16'
        group by product_id
    )
) p2
on p1.product_id=p2.product_id

使用union #

select product_id,new_price price 
from Products 
where (product_id,change_date) in 
(select product_id,max(change_date) from Products where change_date <= '2019-08-16' group by product_id) 
union 
select product_id,10 price from Products group by product_id having min(change_date) > '2019-08-16

使用窗口函数 #

SELECT DISTINCT product_id, IF(filter_date IS NULL, 10, new_price) AS price
FROM (
  SELECT *, RANK() OVER(PARTITION BY product_id ORDER BY filter_date DESC) AS RANKING
  FROM (
    SELECT *, IF(change_date > '2019-08-16', NULL, change_date) AS filter_date
    FROM Products
  ) T
) TT
WHERE TT.RANKING = 1
顶部 首页