2021-06-09

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

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

展示效果：

+--------------------+---------+
| product_name       | unit    |
+--------------------+---------+
| Leetcode Solutions | 130     |
| Leetcode Kit       | 100     |
+--------------------+---------+

Create table If Not Exists 75_Products (product_id int, product_name varchar(40), product_category varchar(40));
Create table If Not Exists 75_Orders (product_id int, order_date date, unit int);
Truncate table 75_Products;
insert into 75_Products (product_id, product_name, product_category) values ('1', 'Leetcode Solutions', 'Book');
insert into 75_Products (product_id, product_name, product_category) values ('2', 'Jewels of Stringology', 'Book');
insert into 75_Products (product_id, product_name, product_category) values ('3', 'HP', 'Laptop');
insert into 75_Products (product_id, product_name, product_category) values ('4', 'Lenovo', 'Laptop');
insert into 75_Products (product_id, product_name, product_category) values ('5', 'Leetcode Kit', 'T-shirt');
Truncate table 75_Orders;
insert into 75_Orders (product_id, order_date, unit) values ('1', '2020-02-05', '60');
insert into 75_Orders (product_id, order_date, unit) values ('1', '2020-02-10', '70');
insert into 75_Orders (product_id, order_date, unit) values ('2', '2020-01-18', '30');
insert into 75_Orders (product_id, order_date, unit) values ('2', '2020-02-11', '80');
insert into 75_Orders (product_id, order_date, unit) values ('3', '2020-02-17', '2');
insert into 75_Orders (product_id, order_date, unit) values ('3', '2020-02-24', '3');
insert into 75_Orders (product_id, order_date, unit) values ('4', '2020-03-01', '20');
insert into 75_Orders (product_id, order_date, unit) values ('4', '2020-03-04', '30');
insert into 75_Orders (product_id, order_date, unit) values ('4', '2020-03-04', '60');
insert into 75_Orders (product_id, order_date, unit) values ('5', '2020-02-25', '50');
insert into 75_Orders (product_id, order_date, unit) values ('5', '2020-02-27', '50');
insert into 75_Orders (product_id, order_date, unit) values ('5', '2020-03-01', '50');

最终sql：

SELECT
		t2.product_name,
		t2.unit
FROM
	(SELECT
	t1.product_name,
	t1.date,
	sum(unit) unit
FROM
	(select
	o.product_id,
	p.product_name,
	DATE_FORMAT(o.order_date,'%Y-%m') date,
	o.unit
from
	75_products p
LEFT join
	75_orders o
on 
	p.product_id = o.product_id) t1
GROUP BY
	product_name,date) t2
WHERE 
	date = "2020-02"
and
	unit >= 100