1164. Product Price at a Given Date----union /case when + null

最新推荐文章于 2021-07-14 23:43:03 发布

betty1121

最新推荐文章于 2021-07-14 23:43:03 发布

阅读量222

点赞数

分类专栏： medium SQL

本文链接：https://blog.csdn.net/betty1121/article/details/108775527

版权

SQL 同时被 2 个专栏收录

61 篇文章 0 订阅

订阅专栏

medium

24 篇文章 0 订阅

订阅专栏

该博客介绍了一个SQL查询，用于从产品价格变更表中检索2019-08-16这一天所有产品的价格。如果产品在那天没有价格变更，则默认价格为10。查询使用了窗口函数row_number()来确定每个产品最新的价格记录，并结合UNION操作符处理那些在指定日期之后有变更但当天价格未记录的产品。

摘要由CSDN通过智能技术生成

Table: Products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+
(product_id, change_date) is the primary key of this table.
Each row of this table indicates that the price of some product was changed to a new price at some date.

Write an SQL query to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.

The query result format is in the following example:

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

with tmp as
(select product_id, new_price as price,
row_number() over (partition by product_id order by change_date desc) num
from products
where change_date <= '2019-08-16') 
select product_id, price
from tmp
where num = 1
union 
select product_id, 10 as price
from (select distinct product_id
from products
where change_date > '2019-08-16'
and product_id not in (select distinct product_id from tmp)) t2

SELECT A.product_id, 
CASE WHEN M.new_price IS NULL THEN 10 ELSE M.new_price END AS 'price'
FROM
(SELECT DISTINCT product_id FROM Products) A
LEFT JOIN
(SELECT product_id, new_price, 
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS 'rnk'
FROM Products
WHERE change_date <= '2019-08-16') M
ON A.product_id = M.product_id
WHERE rnk = 1 OR rnk IS NULL