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

【背景】：物流公司每月需要对货物运输情况进行统计分析，现在需要你根据下面三个数据表，查询出每个运输方式在不同城市的平均运输时长以及总运输费用。
【原始表】：
transport_detail（运输详情）表:

transport_id (运输方式 ID): 运输方式的唯一标识符
transport_name (运输方式名称): 运输方式的名称，如航空、铁路、公路
capacity (运输容量): 运输工具的最大承载量

order_info（订单信息）表:

order_id (订单 ID): 订单的唯一标识符
transport_id (运输方式 ID): 运输方式的唯一标识符，用于关联运输方式表中的运输方式
destination_city (目的地城市): 货物送达的城市
order_date (下单日期): 下单时间
delivery_date (交货日期): 交货时间

cost_data（费用数据）表:

cost_id (费用 ID): 费用的唯一标识符
order_id (订单 ID): 订单的唯一标识符，用于关联订单表中的订单
total_cost (总费用): 运输该订单的总费用

【要求】：：根据上面这三个表格，按照运输方式分组查询每个运输方式在不同城市的平均运输时长（交货日期 - 下单日期）以及总运输费用。查询出来的数据按照destination_city字段升序排列，如果destination_city一致，按照transport_name升序排列。要求查询出来的表格的字段如下：

destination_city : 目的地城市。
transport_name: 运输方式名称。
average_transport_duration: 平均运输时长（以天为单位）。round方式保留2位小数
total_transport_cost: 总运输费用。round方式保留2位小数

【示例】

transport_detail（运输详情）表:

order_info（订单信息）表:

cost_data（费用数据）表:

【按照要求查询出来的表】

【解释】

上述示例中对于订单ID是3和5两条数据，目的地都是广州，运输方式都是铁路，运费总金额是1000+1100 = 2100，平均运输时长是（4+3）/2 = 3.5天

输入

DROP TABLE IF EXISTS transport_detail;
DROP TABLE IF EXISTS order_info;
DROP TABLE IF EXISTS cost_data;
-- 创建表
CREATE TABLE transport_detail (
    transport_id INT PRIMARY KEY,
    transport_name VARCHAR(50),
    capacity INT
);

CREATE TABLE order_info (
    order_id INT PRIMARY KEY,
    transport_id INT,
    destination_city VARCHAR(50),
    order_date DATE,
    delivery_date DATE
);

CREATE TABLE cost_data (
    cost_id INT PRIMARY KEY,
    order_id INT,
    total_cost DECIMAL(10, 2)
);

-- 插入数据
INSERT INTO transport_detail (transport_id, transport_name, capacity)
VALUES (1, '航空', 1000),
       (2, '铁路', 5000);

INSERT INTO order_info (order_id, transport_id, destination_city, order_date, delivery_date)
VALUES (1, 1, '北京', '2024-01-01', '2024-01-03'),
       (2, 1, '上海', '2024-01-05', '2024-01-07'),
       (3, 2, '广州', '2024-02-01', '2024-02-05'),
       (4, 1, '广州', '2024-02-01', '2024-02-02'),
       (5, 2, '广州', '2024-02-01', '2024-02-04');

INSERT INTO cost_data (cost_id, order_id, total_cost)
VALUES (1, 1, 500),
       (2, 2, 800),
       (3, 3, 1000),
       (4, 4, 1500),
       (5, 5, 1100);


select * from transport_detail;
select * from order_info;
select * from cost_data;

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

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

输入

输出

问题信息

热门推荐

相关试题