题解 | 查询出每个运输方式在不同城市的平均运输时长以及总运输费用

with s as(
    select o.transport_id as transport_id,
    o.destination_city as destination_city,
    timestampdiff(day,o.order_date,o.delivery_date) as transport_duration,
    c.total_cost as total_cost
    from order_info o join cost_data c on o.order_id=c.order_id)
select s.destination_city,t.transport_name,
round(avg(s.transport_duration),2) as average_transport_duration,
round(sum(s.total_cost),2) as total_transport_cost
from s join transport_detail t on s.transport_id=t.transport_id
group by s.destination_city,t.transport_name
order by s.destination_city,t.transport_name