Leetcode1164.指定日期的产品价格（中等）

题目
产品数据表: Products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+

这张表的主键是 (product_id, change_date)。
这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。

写一段 SQL来查找在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10。

查询结果格式如下例所示：

Products table:

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

Result table:

+------------+-------+
| product_id | price |
+------------+-------+
| 2          | 50    |
| 1          | 35    |
| 3          | 10    |
+------------+-------+

解答
先查询截止2019-08-16的记录

select *
from Products as P
where P.change_date < '2019-08-16'

对product_id进行分组 选出每个id对应的最大时间 也就是最后更新的时间

select P.product_id, max(P.change_date) as date
from Products as P
where P.change_date < '2019-08-16'
group by P.product_id

用二元in选出 2019-08-16 时产品的价格(这里只包含已经更新的产品)

select PP.product_id, PP.new_price, PP.change_date
from Products as PP
where (PP.product_id, PP.change_date) in (select P.product_id, max(P.change_date) as date
from Products as P
where P.change_date < '2019-08-16'
group by P.product_id)

产品3是在2019-08-16后更新的 应该显示10 这里利用left join即可

select A.product_id, ifnull(B.new_price, 10) as price
-- 选出所有潜在的产品id
from (select distinct product_id
from Products) as A
left join (select PP.product_id, PP.new_price, PP.change_date
from Products as PP
where (PP.product_id, PP.change_date) in (select P.product_id, max(P.change_date) as date
from Products as P
where P.change_date < '2019-08-16'
group by P.product_id)) as B
on A.product_id = B.product_id;

也可以使用union
之前有这样的结果
用二元in选出 2019-08-16 时产品的价格(这里只包含已经更新的产品)

select PP.product_id, PP.new_price, PP.change_date
from Products as PP
where (PP.product_id, PP.change_date) in (select P.product_id, max(P.change_date) as date
from Products as P
where P.change_date < '2019-08-16'
group by P.product_id)

后续只需要把没有更新的产品补充即可

SELECT DISTINCT product_id, 10 AS price
FROM Products
WHERE product_id NOT IN (SELECT product_id 
FROM Products 
WHERE change_date <= '2019-08-16')

两表union有

select PP.product_id, PP.new_price as price
from Products as PP
where (PP.product_id, PP.change_date) in (select P.product_id, max(P.change_date) as date
from Products as P
where P.change_date < '2019-08-16'
group by P.product_id)

union 

SELECT DISTINCT product_id, 10 AS price
FROM Products
WHERE product_id NOT IN (SELECT product_id 
FROM Products 
WHERE change_date <= '2019-08-16')