LeetCode 1327.列出指定时间段内所有的下单产品

最新推荐文章于 2024-10-20 21:47:19 发布

奋斗哼哼

最新推荐文章于 2024-10-20 21:47:19 发布

阅读量47

点赞数

文章标签： leetcode 算法 mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_51696882/article/details/130506544

版权

数据准备

drop table Orders;
drop table Products;

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');

需求

写一个 SQL 语句，要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。

输入

在这里插入图片描述

输出

两表关联,求出2020年2月份的数据

-- 两表关联,求出2020年2月份的数据
select *
from Orders o ,Products p
where o.product_id=p.product_id and substr(order_date,1,7)='2020-02'

在这里插入图片描述

以商品名称分组,求出2020年2月各商品总的下单属性,并筛选出下单数量>=100的数据

-- 以商品名称分组,求出2020年2月各商品总的下单属性,并筛选出下单数量>=100的数据
select product_name,
       sum(unit) as sum_total
from Orders o ,Products p
where o.product_id=p.product_id and substr(order_date,1,7)='2020-02'
group by product_name
having sum_total>=100 ;