select
case when vehicle_fence.type=0 and vehicle_fence.TriggleStatus=0 then 'inside'
when vehicle_fence.type=0 and vehicle_fence.TriggleStatus=1 then 'outside'
when vehicle_fence.type=1 and vehicle_fence.TriggleStatus=1 then 'in'
when vehicle_fence.type=1 and vehicle_fence.TriggleStatus=1 then 'out'
else 'nostatus' end as'围栏状态'
case when vehicle_fence.type=0 and vehicle_fence.TriggleStatus=0 then 'inside'
when vehicle_fence.type=0 and vehicle_fence.TriggleStatus=1 then 'outside'
when vehicle_fence.type=1 and vehicle_fence.TriggleStatus=1 then 'in'
when vehicle_fence.type=1 and vehicle_fence.TriggleStatus=1 then 'out'
else 'nostatus' end as'围栏状态'
from t_vehicleandfence vehicle_fence
select distinct vehicle_info.vin, vehicle_info.organization_id, '0'as'入库状态', -- 围栏内:类型0,状态0;围栏外:类型0,状态1; -- 围栏内:类型1,状态1;围栏外:类型1,状态0; -- status_fence:围栏状态:0:默认值 ,1:围栏内;2:围栏外 case when vehicle_fence.fencetype=0 and vehicle_fence.TriggleStatus=0 then 1 when vehicle_fence.fencetype=0 and vehicle_fence.TriggleStatus=1 then 2 when vehicle_fence.fencetype=1 and vehicle_fence.TriggleStatus=1 then 1 when vehicle_fence.fencetype=1 and vehicle_fence.TriggleStatus=0 then 2 else 0 end as'围栏状态', -- 状态:1:终端非法拔出 ;2:终端未绑定车辆;-- 0:未配车 1:已绑定 2:在途 3:(到达)入库 4:超时未绑定 5:待解绑 6:解绑出库 7:所属部门不一致 9:终端未入库 10:未绑定 case when vehicle_info.vehicle_status=0 then 2 when vehicle_info.vehicle_status=2 then 2 when vehicle_info.vehicle_status=3 then 2 when vehicle_info.vehicle_status=4 then 2 when vehicle_info.vehicle_status=7 then 2 when vehicle_info.vehicle_status=9 then 2 when vehicle_info.vehicle_status=10 then 2 when t_location.warningid=162 then 1 else '0' end as '车和终端关系', -- 车辆是否是离线:转速为0,并且上传数据间隔大于5分钟 case when round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.update_time))/60)>5 and t_location.tachometer!=0 then 2 else 1 end as '是否离线', now()as save_time,now()as update_time,vehicle_info.operator_id from t_vehicle_info vehicle_info INNER JOIN t_vehicleandfence vehicle_fence on vehicle_fence.vehicleid=vehicle_info.id inner join t_vehiclelocation t_location on vehicle_info.vin=t_location.vinNumber