题解 | 统计快递运输时长

SELECT
    exp_type,
    ROUND(AVG(in_time-out_time), 1) AS time
FROM (
    SELECT
        exp_type,
        DAY(in_time)*24+HOUR(in_time)+MINUTE(in_time)/60+SECOND(in_time)/3600 AS in_time,
        DAY(out_time)*24+HOUR(out_time)+MINUTE(out_time)/60+SECOND(out_time)/3600 AS out_time
    FROM exp_action_tb JOIN express_tb
    ON exp_action_tb.exp_number = express_tb.exp_number) t
GROUP BY exp_type
ORDER BY time