orcal时间差

 

/*order_time在数据库中显示的是vchar类型，这里把它转换成了data类型*/

select t1.order_source_type_id ,sum(average)/count(order_sn),count(order_sn) from (
  select t1.order_sn,t1.order_source_type_id, ((sum(to_date(t2.create_time,'yyyy-mm-dd hh24:mi:ss')-  
      to_date(t1.order_time,'yyyy-mm-dd hh24:mi:ss')))/(count(tracking_no)) * 24)  as average
 from crm_order t1,crm_order_shipno t2
  where t1.order_sn=t2.order_sn
  and t1.order_type=75801
  and t2.create_time>'2015-10-01 11:46:44' and t2.create_time <'2015-10-02 11:50:51' 

 and t1.order_source_type_id=75203 

  group by t1.order_sn,t1.order_source_type_id

  ) t1

group by t1.order_source_type_id