LeetCode-1511. 消费者下单频率

最新推荐文章于 2023-08-27 16:07:22 发布

边界流浪者

最新推荐文章于 2023-08-27 16:07:22 发布

阅读量202

点赞数

分类专栏：数据库 LeetCode 网络安全文章标签： mysql 数据库 leetcode

本文链接：https://blog.csdn.net/qq_16542775/article/details/123616526

版权

LeetCode 同时被 3 个专栏收录

251 篇文章

订阅专栏

数据库

31 篇文章

订阅专栏

网络安全

5 篇文章

订阅专栏

编写一个SQL查询，从Customers, Product和Orders表中获取在2020年6月和7月每月消费至少$100的客户ID和姓名。查询结果应显示在指定两个月内满足条件的客户。" 106077995,9362741,JavaWeb实战：过滤器Filter详细教程,"['JavaWeb开发', '过滤器', 'Servlet']

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

表: Customers

表: Product

表: Orders

写一个 SQL 查询，报告在 2020 年 6 月和 7 月每个月至少花费 $100 的客户的 customer_id 和 customer_name 。

以任意顺序返回结果表.

查询结果格式如下例所示。

示例 1:

Product table:
+--------------+-------------+-------------+
| product_id | description | price     |
+--------------+-------------+-------------+
| 10    | LC Phone    | 300   |
| 20   | LC T-Shirt | 10 |
| 30   | LC Book   | 45 |
| 40 | LC Keychain | 2   |
+--------------+-------------+-------------+

Orders table:
+--------------+-------------+-------------+-------------+-----------+
| order_id | customer_id | product_id | order_date | quantity |
+--------------+-------------+-------------+-------------+-----------+
| 1     | 1     | 10 | 2020-06-10 | 1 |
| 2    | 1 | 20 | 2020-07-01 | 1 |
| 3    | 1 | 30 | 2020-07-08 | 2 |
| 4     | 2     | 10 | 2020-06-15 | 2 |
| 5    | 2 | 40 | 2020-07-01 | 10 |
| 6    | 3 | 20 | 2020-06-24 | 2 |
| 7     | 3     | 30 | 2020-06-25 | 2 |
| 9    | 3 | 30 | 2020-05-08 | 3 |
+--------------+-------------+-------------+-------------+-----------+

输出：
+--------------+------------+
| customer_id | name |
+--------------+------------+
| 1 | Winston |
+--------------+------------+
解释：
Winston 在2020年6月花费了$300(300 * 1), 在7月花费了$100(10 * 1 + 45 * 2).
Jonathan 在2020年6月花费了$600(300 * 2), 在7月花费了$20(2 * 10).
Moustafa 在2020年6月花费了$110 (10 * 2 + 45 * 2), 在7月花费了$0.

# Write your MySQL query statement below

SELECT cu.customer_id, cu.name FROM Customers AS cu
INNER JOIN
(
SELECT l.customer_id FROM
(
SELECT g.customer_id FROM
(
SELECT t.customer_id, SUM(p.price * t.quantity) AS prices 
FROM
(
    SELECT *FROM Orders 
    WHERE order_date like '2020-06%'
) AS t
LEFT JOIN Product AS p 
ON t.product_id = p.product_id
GROUP BY t.customer_id
) AS g
WHERE g.prices >= 100
) 
AS l

INNER JOIN
(
SELECT g.customer_id FROM
(
SELECT t.customer_id, SUM(p.price * t.quantity) AS prices 
FROM
(
    SELECT *FROM Orders 
    WHERE order_date like '2020-07%'
) AS t
LEFT JOIN Product AS p 
ON t.product_id = p.product_id
GROUP BY t.customer_id
) AS g
WHERE g.prices >= 100
)
AS r
ON l.customer_id = r.customer_id
)
AS f
ON cu.customer_id = f.customer_id;