阅读量:0
文章目录
力扣高频SQL 50题（基础版）第三十九题
1327.列出指定时间段内所有的下单产品
题目说明
表: Products
±-----------------±--------+
| Column Name | Type |
±-----------------±--------+
| product_id | int |
| product_name | varchar |
| product_category | varchar |
±-----------------±--------+
product_id 是该表主键(具有唯一值的列)。
该表包含该公司产品的数据。
表: Orders
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| product_id | int |
| order_date | date |
| unit | int |
±--------------±--------+
该表可能包含重复行。
product_id 是表单 Products 的外键（reference 列）。
unit 是在日期 order_date 内下单产品的数目。
写一个解决方案，要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。
返回结果表单的 顺序无要求 。
实现过程
准备数据
Create table If Not Exists Products (product_id int, product_name varchar(40), product_category varchar(40)) Create table If Not Exists Orders (product_id int, order_date date, unit int) Truncate table Products insert into Products (product_id, product_name, product_category) values ('1', 'Leetcode Solutions', 'Book') insert into Products (product_id, product_name, product_category) values ('2', 'Jewels of Stringology', 'Book') insert into Products (product_id, product_name, product_category) values ('3', 'HP', 'Laptop') insert into Products (product_id, product_name, product_category) values ('4', 'Lenovo', 'Laptop') insert into Products (product_id, product_name, product_category) values ('5', 'Leetcode Kit', 'T-shirt') Truncate table Orders insert into Orders (product_id, order_date, unit) values ('1', '2020-02-05', '60') insert into Orders (product_id, order_date, unit) values ('1', '2020-02-10', '70') insert into Orders (product_id, order_date, unit) values ('2', '2020-01-18', '30') insert into Orders (product_id, order_date, unit) values ('2', '2020-02-11', '80') insert into Orders (product_id, order_date, unit) values ('3', '2020-02-17', '2') insert into Orders (product_id, order_date, unit) values ('3', '2020-02-24', '3') insert into Orders (product_id, order_date, unit) values ('4', '2020-03-01', '20') insert into Orders (product_id, order_date, unit) values ('4', '2020-03-04', '30') insert into Orders (product_id, order_date, unit) values ('4', '2020-03-04', '60') insert into Orders (product_id, order_date, unit) values ('5', '2020-02-25', '50') insert into Orders (product_id, order_date, unit) values ('5', '2020-02-27', '50') insert into Orders (product_id, order_date, unit) values ('5', '2020-03-01', '50') 实现方式
with t1 as(select p.product_name, sum(o.unit) over(partition by o.product_id) as unit from orders o join products p on o.product_id=p.product_id where o.order_date between '2020-02-01' and '2020-02-29') select distinct product_name,unit from t1 where unit>=100; 结果截图
