# 题解 | #SQL 19.2021年国庆在北京接单3次及以上的司机统计信息#

2021年国庆在北京接单3次及以上的司机统计信息

http://www.nowcoder.com/practice/992783fd80f746d49e790d33ee537c19

# 2021年国庆在北京接单3次及以上的司机统计信息

### 问题分解：

• 计算2021国庆在北京接单至少3次的司机信息：
• 关联接单表和打车记录表：tb_get_car_order JOIN tb_get_car_record USING(order_id)
• 筛选北京国庆期间的记录：`WHERE city = "北京" and DATE_FORMAT(order_time,"%Y%m%d") BETWEEN '20211001' AND '20211007'`
• 按司机ID分组：GROUP BY driver_id
• 统计每个司机的接单量和接单收入：COUNT(order_id) as order_num, SUM(fare) as income
• 筛选接单至少3次的分组（司机）：HAVING COUNT(order_id) >= 3
• 基于上述结果，统计他们的平均接单量和平均收入：
• 平均接单量：AVG(order_num) as avg_order_num
• 平均收入：AVG(income) as avg_income
• 保留3位小数：ROUND(x, 3)

• 表头重命名：as

### 完整代码：

``````SELECT "北京" as city, ROUND(AVG(order_num), 3) as avg_order_num,
ROUND(AVG(income), 3) as avg_income
FROM (
SELECT driver_id, COUNT(order_id) as order_num, SUM(fare) as income
FROM tb_get_car_order
JOIN tb_get_car_record USING(order_id)
WHERE city = "北京" and DATE_FORMAT(order_time,"%Y%m%d") BETWEEN '20211001' AND '20211007'
GROUP BY driver_id
HAVING COUNT(order_id) >= 3
) as t_driver_info;
``````
SQL大厂真题 文章被收录于专栏

1

select '北京' as city,round(avg(order_num),3) as avg_order_num,round(avg(fare_num),3) as avg_income from ( select driver_id,count(a.order_id) order_num,sum(fare) as fare_num from tb_get_car_order a left join tb_get_car_record b on a.uid=b.uid where city='北京' and date(event_time) between '2021-10-01' and '2021-10-07' and order_time is not null group by driver_id having count(a.order_id)>=3 ) t; 请问这样为什么不对呢

select "北京" as city, round(avg(num),3) avg_order_num, avg(income) avg_income from( select driver_id, count(order_id) num, sum(fare) income from tb_get_car_record right join tb_get_car_order using(order_id) where city = '北京' and order_time between'2021-10-01' and '2021-10-07' group by driver_id having num > 3 )t1

28 2 评论