❤️博客主页: 楚生辉
❤️系列专栏:【LeetCode刷题】
❤️一句短话: 坚持不懈,孜孜不倦
1.题目描述
产品数据表: Products
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| new_price | int |
| change_date | date |
+---------------+---------+
这张表的主键是 (product_id, change_date)。
这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。
写一段 SQL来查找在 2019-08-16
时全部产品的价格,假设所有产品在修改前的价格都是 10
。
以 任意顺序 返回结果表。
查询结果格式如下例所示。
输入:
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 |
+------------+-------+
2.代码实现
思路实现:先筛选出change_date
在2019-08-16之前最大的new_price
以及id,在用它与Products进行连接筛选,然后再以把所有的id查询出来进行表与表之间的左连接,最后进行null值处理。
SELECT
t1.product_id AS product_id,
IF( new_price IS NULL, 10, new_price ) AS price
FROM
( SELECT DISTINCT product_id FROM Products ) t1
LEFT JOIN (
SELECT
t1.product_id AS product_id,
t1.new_price
FROM
Products t1
JOIN ( SELECT product_id, max( change_date ) AS DATA FROM Products WHERE change_date <= '2019-08-16' GROUP BY product_id ) t2 ON t1.product_id = t2.product_id
AND t1.change_date = t2.DATA
) t2 ON t1.product_id = t2.product_id
减少一层连接,通过多列in
去优化join连接,每少一层连接,就会极大的增加效率
SELECT DISTINCT
a.product_id,
ifnull( b.new_price, 10 ) AS price
FROM
Products a
LEFT JOIN (
SELECT
product_id,new_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 )) b
ON a.product_id = b.product_id