某物流公司快递信息数据及运输动作信息数据如下： 快递信息表express_tb(exp_number-快递单号,exp_type-快递种类,out_city-快递发出城市,in_city-快递邮入城市,create_time-快递单创建时间)，如下所示： 快递运输动作表exp_action_tb(exp_number-快递单号,transport_type-运输类型,out_time-快递发出时间,in_time-快递到达时间)，如下所示： 问题：请统计每种快递种类平均运输时长? 要求输出：快递种类、平均运输时长（单位：小时，保留1位小数） 知识点：关联查询、分组查询、聚合函数、时间函数 示例数据结果如下： 解释：快递种类为C&A的单号有11102：发出时间为2022-05-03 09:00:00，邮入时间为：2022-05-06 09:00:00，则间隔时长为72.0小时； 11106：发出时间为2022-05-03 09:00:00，邮入时间为：2022-05-06 16:00:00，则间隔时长为79.0小时； 则快递种类为C&A的平均运输时长为：75.5小时 其他快递种类同理
示例1

输入

drop table if exists  `express_tb` ; 
CREATE TABLE `express_tb` (
`exp_number` int(11) NOT NULL,
`exp_type` varchar(16) NOT NULL,
`out_area` varchar(16) NOT NULL,
`in_area` varchar(16) NOT NULL,
`create_time` datetime NULL,
PRIMARY KEY (`exp_number`));
INSERT INTO express_tb VALUES(11101,'file','city1','city2','2022-05-02 09:00:00');  
INSERT INTO express_tb VALUES(11102,'C&A','city3','city4','2022-05-02 09:00:05');
INSERT INTO express_tb VALUES(11103,'file','city1','city4','2022-05-02 09:00:12');
INSERT INTO express_tb VALUES(11104,'food','city1','city2','2022-05-02 09:00:15');
INSERT INTO express_tb VALUES(11105,'food','city2','city3','2022-05-02 09:00:35');
INSERT INTO express_tb VALUES(11106,'C&A','city1','city3','2022-05-02 09:01:00');
INSERT INTO express_tb VALUES(11107,'file','city2','city1','2022-05-02 09:01:23');
INSERT INTO express_tb VALUES(11108,'digitalproduct','city3','city2','2022-05-02 09:01:30');

drop table if exists  `exp_action_tb` ;   
CREATE TABLE `exp_action_tb` (
`exp_number` int(11) NOT NULL,
`transport_type` varchar(16) NOT NULL,
`out_time` datetime NOT NULL,
`in_time` datetime NOT NULL,
PRIMARY KEY (`exp_number`));
INSERT INTO exp_action_tb VALUES(11101,'air_transport','2022-05-03 09:00:00','2022-05-03 13:00:00');  
INSERT INTO exp_action_tb VALUES(11102,'road_transport','2022-05-03 09:00:00','2022-05-06 09:00:00');  
INSERT INTO exp_action_tb VALUES(11103,'air_transport','2022-05-03 10:00:00','2022-05-03 15:00:00');  
INSERT INTO exp_action_tb VALUES(11104,'rail_transport','2022-05-03 10:00:00','2022-05-04 12:00:00');  
INSERT INTO exp_action_tb VALUES(11105,'rail_transport','2022-05-03 12:00:00','2022-05-04 11:00:00');  
INSERT INTO exp_action_tb VALUES(11106,'road_transport','2022-05-03 09:00:00','2022-05-06 16:00:00');  
INSERT INTO exp_action_tb VALUES(11107,'air_transport','2022-05-03 11:00:00','2022-05-03 17:00:00');  
INSERT INTO exp_action_tb VALUES(11108,'air_transport','2022-05-03 10:00:00','2022-05-03 13:00:00');

输出

exp_type|time
digitalproduct|3.0
file|5.0
food|24.5
C&A|75.5
加载中...