# Write your MySQL query statement below
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 的外键。 unit 是在日期 order_date 内下单产品的数目。
写一个 SQL 语句，要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。
返回结果表单的 顺序无要求 。
查询结果的格式如下。
示例 1:
输入： Products 表: +-------------+-----------------------+------------------+ | product_id | product_name | product_category | +-------------+-----------------------+------------------+ | 1 | Leetcode Solutions | Book | | 2 | Jewels of Stringology | Book | | 3 | HP | Laptop | | 4 | Lenovo | Laptop | | 5 | Leetcode Kit | T-shirt | +-------------+-----------------------+------------------+ Orders 表: +--------------+--------------+----------+ | product_id | order_date | unit | +--------------+--------------+----------+ | 1 | 2020-02-05 | 60 | | 1 | 2020-02-10 | 70 | | 2 | 2020-01-18 | 30 | | 2 | 2020-02-11 | 80 | | 3 | 2020-02-17 | 2 | | 3 | 2020-02-24 | 3 | | 4 | 2020-03-01 | 20 | | 4 | 2020-03-04 | 30 | | 4 | 2020-03-04 | 60 | | 5 | 2020-02-25 | 50 | | 5 | 2020-02-27 | 50 | | 5 | 2020-03-01 | 50 | +--------------+--------------+----------+ 输出： +--------------------+---------+ | product_name | unit | +--------------------+---------+ | Leetcode Solutions | 130 | | Leetcode Kit | 100 | +--------------------+---------+ 解释： 2020 年 2 月份下单 product_id = 1 的产品的数目总和为 (60 + 70) = 130 。 2020 年 2 月份下单 product_id = 2 的产品的数目总和为 80 。 2020 年 2 月份下单 product_id = 3 的产品的数目总和为 (2 + 3) = 5 。 2020 年 2 月份 product_id = 4 的产品并没有下单。 2020 年 2 月份下单 product_id = 5 的产品的数目总和为 (50 + 50) = 100 。
原站题解
pythondata 解法, 执行用时: 393 ms, 内存消耗: 67.2 MB, 提交时间: 2024-05-27 11:16:57
import pandas as pd
def list_products(products: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:
orders = orders[orders['order_date'].dt.strftime('%Y-%m')=='2020-02'][['product_id', 'unit']]
orders = orders.groupby(['product_id']).sum().reset_index()
orders = orders[orders['unit'] >= 100]
df = orders.merge(products, how='left', on='product_id')
return df[['product_name', 'unit']]
# 两组条件逼近
def list_products2(products: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:
c = orders[(orders["order_date"] > "2020-01-31") & (orders["order_date"] < "2020-03-01")]
d = c.groupby("product_id").agg({"unit":"sum"}).reset_index()
e = products.merge(d,how = "left",on = "product_id").fillna(0)
return e[e["unit"] >= 100][["product_name","unit"]]
mysql 解法, 执行用时: 412 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:44:07
# Write your MySQL query statement below
SELECT
p.product_name,
SUM(o.unit) AS UNIT
FROM
Orders AS o
INNER JOIN Products AS p
ON o.product_id = p.product_id
WHERE
YEAR(o.order_date) = 2020 AND MONTH(o.order_date) = 2
GROUP BY
o.product_id
HAVING
SUM(o.unit) >= 100;
mysql 解法, 执行用时: 392 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:43:40
# Write your MySQL query statement below select p.product_name,o.unit from(select distinct product_id,sum(unit) as unit from Orders where order_date between '2020-02-01 00:00:00' and '2020-02-29 00:00:00' group by product_id) as o,Products as p where o.unit >= 100 and p.product_id = o.product_id;
mysql 解法, 执行用时: 390 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:43:15
# Write your MySQL query statement below SELECT product_name, SUM(unit) AS unit FROM Products JOIN Orders USING (product_id) WHERE order_date LIKE "2020-02%" GROUP BY product_name HAVING unit >= 100;
mysql 解法, 执行用时: 410 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:42:33
# Write your MySQL query statement below
select
product_name,
sum(unit) unit
from Orders O
left join Products P using(product_id)
where order_date like '2020-02%'
group by product_name
having unit >= 100