- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
目录
一,原题力扣链接
二,题干
配送表:
Delivery
+-----------------------------+---------+ | Column Name | Type | +-----------------------------+---------+ | delivery_id | int | | customer_id | int | | order_date | date | | customer_pref_delivery_date | date | +-----------------------------+---------+ delivery_id 是表的主键(具有唯一值的列)。 该表保存着顾客的食物配送信息,顾客在某个日期下了订单,并指定了一个期望的配送日期(和下单日期相同或者在那之后)。如果顾客期望的配送日期和下单日期相同,则该订单称为 「即时订单」,否则称为「计划订单」。
编写解决方案统计即时订单所占的百分比, 保留两位小数。
返回结果如下所示。
示例 1:
输入: Delivery 表: +-------------+-------------+------------+-----------------------------+ | delivery_id | customer_id | order_date | customer_pref_delivery_date | +-------------+-------------+------------+-----------------------------+ | 1 | 1 | 2019-08-01 | 2019-08-02 | | 2 | 5 | 2019-08-02 | 2019-08-02 | | 3 | 1 | 2019-08-11 | 2019-08-11 | | 4 | 3 | 2019-08-24 | 2019-08-26 | | 5 | 4 | 2019-08-21 | 2019-08-22 | | 6 | 2 | 2019-08-11 | 2019-08-13 | +-------------+-------------+------------+-----------------------------+ 输出: +----------------------+ | immediate_percentage | +----------------------+ | 33.33 | +----------------------+ 解释:2 和 3 号订单为即时订单,其他的为计划订单。
三,建表语句
Create table If Not Exists Delivery (delivery_id int, customer_id int, order_date date, customer_pref_delivery_date date);
Truncate table Delivery;
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('1', '1', '2019-08-01', '2019-08-02');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('2', '5', '2019-08-02', '2019-08-02');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('3', '1', '2019-08-11', '2019-08-11');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('4', '3', '2019-08-24', '2019-08-26');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('5', '4', '2019-08-21', '2019-08-22');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('6', '2', '2019-08-11', '2019-08-13');
select * from delivery;
四,分析
题解:
表:电子交互表
字段:支付id,顾客id,下单时间,配送时间
求即时订单在中订单的百分比
即时订单= 配送时间-下单时间 =0
思路:
第一步,拿到即时订单的数量
![]()
第二步,拿到订单的中数量
第三步,即时订单数量/总订单数量 *100 最后在四舍五入一下
五,SQL解答
select
round(
(select count(*) from delivery where customer_pref_delivery_date = order_date)/
(select count(*) from delivery)*100,2) as immediate_percentage;
换个写法
select round(count(*) /
(select count(*) from delivery)*100,2) as immediate_percentage
from delivery where customer_pref_delivery_date = order_date;
六,验证
七,知识点总结
- count函数的运用
- round函数的运用
- 百分比 某个/总体
- 子查询的运用
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用