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

with t1 as
(select transport_detail.transport_id,transport_name,destination_city,round(avg(datediff(delivery_date , order_date )),2) average_transport_duration
from transport_detail join order_info on transport_detail.transport_id = order_info.transport_id
group by transport_detail.transport_id,transport_name,destination_city),
t2 as 
(select transport_detail.transport_id,destination_city,round(sum(total_cost ),2) total_transport_cost
from transport_detail join order_info on transport_detail.transport_id =order_info.transport_id join cost_data on order_info.order_id =cost_data.order_id 
group by transport_detail.transport_id,destination_city)

select  t1.destination_city,t1.transport_name,average_transport_duration,total_transport_cost
from t1,t2
where t1.transport_id=t2.transport_id and t1.destination_city=t2.destination_city
order by t1.destination_city,t1.transport_name