题目
产品数据表: 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')
网友评论