原题链接:http://practice.atguigu.cn/#/question/12/desc?qType=SQL
题目需求
查询所有商品(sku_info表)截至到2021年10月01号的最新商品价格(需要结合价格修改表进行分析)
期望结果如下:
sku_id(商品id) | price<decimal(16,2)>(商品价格) |
---|---|
1 | 2000.00 |
2 | 10.00 |
3 | 5000.00 |
4 | 6000.00 |
5 | 500.00 |
6 | 2000.00 |
7 | 100.00 |
8 | 600.00 |
9 | 1000.00 |
10 | 90.00 |
11 | 66.00 |
12 | 20.00 |
需要用到的表:
商品信息表:sku_info
sku_id(商品id) | name(商品名称) | category_id(分类id) | from_date(上架日期) | price(商品价格) |
---|---|---|---|---|
1 | xiaomi 10 | 1 | 2020-01-01 | 2000 |
6 | 洗碗机 | 2 | 2020-02-01 | 2000 |
9 | 自行车 | 3 | 2020-01-01 | 1000 |
商品价格变更明细表:sku_price_modify_detail
sku_id(商品id) | new_price(本次变更之后的价格) | change_date(变更日期) |
---|---|---|
1 | 1900.00 | 2021-09-25 |
1 | 2000.00 | 2021-09-26 |
2 | 80.00 | 2021-09-29 |
2 | 10.00 | 2021-09-30 |
解题思路
SELECT t1.sku_id,
cast(coalesce(t2.new_price,t1.price) AS decimal(16,2)) AS price
FROM
(
SELECT sku_id,
price
FROM sku_info
WHERE from_date <= '2021-10-01'
) t1
LEFT JOIN
(
SELECT sku_id,
new_price,
ROW_NUMBER() OVER (PARTITION BY sku_id ORDER BY change_date DESC) AS rn
FROM sku_price_modify_detail
WHERE change_date <= '2021-10-01'
) t2
ON t1.sku_id = t2.sku_id AND t2.rn = 1