1164. 指定日期的产品价格

English Version

题目描述

产品数据表: Products

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

 

写一段 SQL来查找在 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    |
+------------+-------+

解法

SQL

# Write your MySQL query statement below
SELECT
    p1.product_id AS product_id,
    ifnull(p2.price, 10) AS price
FROM
    (
        SELECT DISTINCT
            (product_id) AS product_id
        FROM Products
    ) AS p1
    LEFT JOIN (
        SELECT
            t1.product_id,
            t1.new_price AS price
        FROM
            Products AS t1
            JOIN (
                SELECT
                    product_id,
                    max(change_date) AS change_date
                FROM Products
                WHERE change_date <= '2019-08-16'
                GROUP BY product_id
            ) AS t2
                ON t1.product_id = t2.product_id
                AND t1.change_date = t2.change_date
    ) AS p2
        ON p1.product_id = p2.product_id;