- 学习：知识的初次邂逅
- 复习：知识的温故知新
- 练习：知识的实践应用
目录
一，原题力扣链接
二，题干
表:
Customers+---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | name | varchar | | country | varchar | +---------------+---------+ customer_id 是该表具有唯一值的列. 该表包含公司消费者的信息.表:
Product+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | description | varchar | | price | int | +---------------+---------+ product_id 是该表具有唯一值的列. 该表包含公司产品的信息. price 是该产品所需的花销.表:
Orders+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | customer_id | int | | product_id | int | | order_date | date | | quantity | int | +---------------+---------+ order_id 是该表具有唯一值的列. 该表包含消费者下单的信息. customer_id 是买了数量为 "quantity", id 为 "product_id" 产品的消费者的 id. Order_date 是订单发货的日期, 格式为('YYYY-MM-DD').写一个解决方案，报告在 2020 年 6 月和 7 月 每个月至少花费
$100的客户的customer_id和customer_name。以 任意顺序 返回结果表.
结果格式如下例所示。
示例 1:
输入：Customers table:+--------------+-----------+-------------+ | customer_id | name | country | +--------------+-----------+-------------+ | 1 | Winston | USA | | 2 | Jonathan | Peru | | 3 | Moustafa | Egypt | +--------------+-----------+-------------+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.
三，建表语句
Create table If Not Exists Customers (customer_id int, name varchar(30), country varchar(30));
Create table If Not Exists Product (product_id int, description varchar(30), price int);
Create table If Not Exists Orders (order_id int, customer_id int, product_id int, order_date date, quantity int);
Truncate table Customers;
insert into Customers (customer_id, name, country) values ('1', 'Winston', 'USA');
insert into Customers (customer_id, name, country) values ('2', 'Jonathan', 'Peru');
insert into Customers (customer_id, name, country) values ('3', 'Moustafa', 'Egypt');
Truncate table Product;
insert into Product (product_id, description, price) values ('10', 'LC Phone', '300');
insert into Product (product_id, description, price) values ('20', 'LC T-Shirt', '10');
insert into Product (product_id, description, price) values ('30', 'LC Book', '45');
insert into Product (product_id, description, price) values ('40', 'LC Keychain', '2');
Truncate table Orders;
insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('1', '1', '10', '2020-06-10', '1');
insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('2', '1', '20', '2020-07-01', '1');
insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('3', '1', '30', '2020-07-08', '2');
insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('4', '2', '10', '2020-06-15', '2');
insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('5', '2', '40', '2020-07-01', '10');
insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('6', '3', '20', '2020-06-24', '2');
insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('7', '3', '30', '2020-06-25', '2');
insert into Orders (order_id, customer_id, product_id, order_date, quantity) values ('9', '3', '30', '2020-05-08', '3');
select * from orders;
select * from product;
select * from customers;
四，分析
题解：
第一张表:订单表
字段：订单id，客户id，产品id，下单时间，下单数量
第二张表：产品表
字段：产品id，产品描述，价格
第三张表：客户表
字段：客户id，客户名称，国家
求：用户在6月和7月都购买商品超过100的 用户id和姓名
分析：
思路一
第一步：连接三个表
用户下单了才有记录，商品被购买了订单表才有记录 所以可以用内连接
第二步，去除无用的字段，添加一个新的字段，总价格，用数量*价格
第三步，给时间做一下处理 截取年和月
然后以 id 姓名，时间分组 并且价格大于等于100
最后一步，使用列合并函数
思路二：分组+过滤+ 使用if
三表连接之后 直接使用if 以id和姓名分组
如果 日期是6月 则返回 数量*价格 反之返回0 用sum 求和 大于等于100
并且 日期是7月 则返回 数量*价格 反之返回0 用sum 求和 大于等于100
五，SQL解答
解法一
with t1 as (
select
c.customer_id,c.name,date_format(o.order_date,'%Y-%m') as ym
from orders o join product p on o.product_id=p.product_id
join customers c on c.customer_id=o.customer_id
group by c.customer_id, c.name, date_format(o.order_date,'%Y-%m') having sum(o.quantity*p.price)>=100
)
select customer_id,name from t1 group by customer_id,name
having group_concat(ym) like '%2020-06%' and
group_concat(ym) like '%2020-07%';解法二
select c.customer_id,c.name
from customers c
join orders o on o.customer_id=c.customer_id
join product p on p.product_id=o.product_id
group by c.customer_id, c.name
having sum(if( date_format(o.order_date,'%Y-%m') ='2020-06',o.quantity*p.price,0)) >=100
and
sum(if( date_format(o.order_date,'%Y-%m') ='2020-07',o.quantity*p.price,0)) >=100;
六，验证
七，知识点总结
- if函数的运用
- 连接查询的运用
- 分组+聚合+过滤的运用
- group_concat 函数的运用
- 时间函数的运用
- 学习：知识的初次邂逅
- 复习：知识的温故知新
- 练习：知识的实践应用
扫一扫
501
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
