【LeetCode | 数据库】指定日期的产品价格

本专栏持续输出 LeetCode 题目集，欢迎订阅。

题目

产品数据表: Products

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

编写一个解决方案，找出在 2019-08-16 所有产品的价格。

以 任意顺序 返回结果表。

结果格式如下例所示。

 

示例 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    |
+------------+-------+

代码

-- 先获取所有产品在2019-08-16及之前的最新价格
WITH latest_prices AS (
    SELECT 
        product_id, 
        new_price AS 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
        )
),
-- 获取所有出现过的产品ID
all_products AS (
    SELECT DISTINCT product_id FROM Products
)

-- 结合以上结果，未找到价格的产品使用初始价格10
SELECT 
    a.product_id,
    COALESCE(l.price, 10) AS price
FROM 
    all_products a
LEFT JOIN 
    latest_prices l ON a.product_id = l.product_id;