/*注意！！！timestampdiff()会截断小数部分，降低精确度！！！！*/
select t.exp_type
,round(avg(time1),1) as time
from(
select exp_type
,timestampdiff(minute,out_time,in_time) / 60  as time1
from express_tb t1
join exp_action_tb t2 on t1.exp_number = t2.exp_number
where out_time < in_time
/*和having语句同作用
and out_time is not null
and in_time is not null
and timestampdiff(hour,out_time,in_time) > 0*/
) t
group by t.exp_type
having round(avg(time1),1) is not null
/*去除异常值，比如任意时间出现的null值*/
order by time