case when ...when...when...when..else.. end 使用情况

SELECT mechanism.organizeName,
sum(case when storageVehicles_status=1 then 1 else 0 end )as storageVehiclesSum,
sum(case when storageVehicles_status=2 then 1 else 0 end )as outboundVehicleSum,
sum(case when storageVehicles_status!=2 then 1 else 0 end ) AS libraryVehicleSum,
sum(case when report_status_terminal=1 then 1 else 0 end )as terminalIllegalDrew,
sum(case when report_status_terminal=2 then 1 else 0 end )as notBoundTerminalSum,
sum(case when report_status_fence=1 then 1 else 0 end )fenceInVehicleSum,
sum(case when report_status_fence=2 then 1 else 0 end )fenceOutVehicleSum,
sum(case when report_status_fence=7 then 1 else 0 end )as waitfenceVehicleSum,
sum(case when OffLineVehicle_Status=2 then 1 else 0 end )OffLineVehicleSum
from (


select * from (
select  DISTINCT
vehicle_info.vin,
vehicle_info.organization_id,
case when date(vehicle_info.add_time ) = curdate() then 1 else 0 end as storageVehicles_status,
-- 围栏内：类型0,状态0；围栏外：类型0,状态1；
-- 围栏内：类型1,状态1；围栏外：类型1,状态0；
-- status_fence:围栏状态：0：默认值 ，1：围栏内；2：围栏外
-- t_location.warningid,
-- vehicle_info.vehicle_status,
-- vehicle_fence.TriggleStatus ,
-- vehicle_fence.TriggerType,
-- vehicle_info.vehicle_status,
case when vehicle_fence.TriggerType is null and vehicle_fence.TriggleStatus is null and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>5 and t_location.tachometer!=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162  then 6
     when vehicle_fence.TriggerType is null and vehicle_fence.TriggleStatus is null and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>750 and t_location.tachometer=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162 then 6
     when vehicle_fence.TriggerType is null and vehicle_fence.TriggleStatus is null and vehicle_info.vehicle_status=1 and t_location.warningid=162 then 5
     
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=0 and vehicle_info.vehicle_status=1 and t_location.warningid=162 then 5
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=0 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>5 and t_location.tachometer!=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162  then 6
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=0 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>750 and t_location.tachometer=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162 then 6
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=0 and vehicle_info.vehicle_status=1 then 1
     
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=1 and vehicle_info.vehicle_status=1 and t_location.warningid=162 then 5
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=1 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>5 and t_location.tachometer!=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162  then 6
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=1 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>750 and t_location.tachometer=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162 then 6
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=1 and vehicle_info.vehicle_status=1 then 2
     
     -- when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=1 and t_location.warningid!=162 and vehicle_info.vehicle_status=1 then 1
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=1 and vehicle_info.vehicle_status=1 and t_location.warningid=162 then 5
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=1 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>5 and t_location.tachometer!=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162  then 6
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=1 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>750 and t_location.tachometer=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162 then 6
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=1 and vehicle_info.vehicle_status=1 then 1
     
     -- when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=0 and t_location.warningid!=162 and vehicle_info.vehicle_status=1 then 2
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=0 and vehicle_info.vehicle_status=1 and t_location.warningid=162 then 5
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=0 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>5 and t_location.tachometer!=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162  then 6
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=0 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>750 and t_location.tachometer=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162 then 6
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=0 and vehicle_info.vehicle_status=1 then 2
     
     when vehicle_fence.TriggerType is null and vehicle_fence.TriggleStatus is null and vehicle_info.vehicle_status=1 then 7
     else 8 end as report_status_fence,
-- 状态：1：终端非法拔出 ;2:终端未绑定车辆；-- 0:未配车  1:已绑定  2:在途 3:(到达)入库 4:超时未绑定 5:待解绑 6:解绑出库  7:所属部门不一致 9:终端未入库 10:未绑定
case when t_location.warningid=162 and vehicle_info.vehicle_status=1 then 1
     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
     else 0 end as report_status_terminal,
-- 车辆是否是离线:转速为0,车速为0，并且上传数据间隔大于5分钟 t_location.tachometer=0 and >12.5小时(720+30=750)
case when round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>5 and t_location.tachometer!=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162  then 2
     when round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>750 and t_location.tachometer=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162 then 2 else 1 end as OffLineVehicle_Status,

now()as save_time,now()as update_time,vehicle_info.operator_id  
from t_vehicle_info vehicle_info
left JOIN t_vehicleandfence vehicle_fence on vehicle_fence.VehicleVin=vehicle_info.vin
left join t_vehiclelocation t_location  on vehicle_info.vin=t_location.vinNumber
-- where
-- vehicle_info.vin='LGBF5AE00DR006742'
-- GROUP BY vehicle_info.vin
ORDER BY  vehicle_info.vin ,report_status_fence asc -- '围栏状态';
) as temp_table
where not EXISTS (
select 1 from(
select  DISTINCT
vehicle_info.vin,
vehicle_info.organization_id,
case when date(vehicle_info.add_time ) = curdate() then 1 else 0 end storageVehicles_status,
-- 围栏内：类型0,状态0；围栏外：类型0,状态1；
-- 围栏内：类型1,状态1；围栏外：类型1,状态0；   ||0:未触发，1:已触发 3:待触发
-- status_fence:围栏状态：0：默认值 ，1：围栏内；2：围栏外
case when vehicle_fence.TriggerType is null and vehicle_fence.TriggleStatus is null and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>5 and t_location.tachometer!=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162  then 6
     when vehicle_fence.TriggerType is null and vehicle_fence.TriggleStatus is null and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>750 and t_location.tachometer=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162 then 6
     when vehicle_fence.TriggerType is null and vehicle_fence.TriggleStatus is null and vehicle_info.vehicle_status=1 and t_location.warningid=162 then 5
     
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=0 and vehicle_info.vehicle_status=1 and t_location.warningid=162 then 5
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=0 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>5 and t_location.tachometer!=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162  then 6
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=0 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>750 and t_location.tachometer=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162 then 6
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=0 and vehicle_info.vehicle_status=1 then 1
     
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=1 and vehicle_info.vehicle_status=1 and t_location.warningid=162 then 5
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=1 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>5 and t_location.tachometer!=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162  then 6
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=1 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>750 and t_location.tachometer=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162 then 6
     when vehicle_fence.TriggerType=0 and vehicle_fence.TriggleStatus=1 and vehicle_info.vehicle_status=1 then 2
     
     -- when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=1 and t_location.warningid!=162 and vehicle_info.vehicle_status=1 then 1
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=1 and vehicle_info.vehicle_status=1 and t_location.warningid=162 then 5
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=1 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>5 and t_location.tachometer!=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162  then 6
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=1 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>750 and t_location.tachometer=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162 then 6
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=1 and vehicle_info.vehicle_status=1 then 1
     
     -- when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=0 and t_location.warningid!=162 and vehicle_info.vehicle_status=1 then 2
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=0 and vehicle_info.vehicle_status=1 and t_location.warningid=162 then 5
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=0 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>5 and t_location.tachometer!=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162  then 6
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=0 and round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>750 and t_location.tachometer=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162 then 6
     when vehicle_fence.TriggerType=1 and vehicle_fence.TriggleStatus=0 and vehicle_info.vehicle_status=1 then 2
     
     when vehicle_fence.TriggerType is null and vehicle_fence.TriggleStatus is null and vehicle_info.vehicle_status=1 then 7
     else 8 end as report_status_fence,-- 状态：1：终端非法拔出 ;2:终端未绑定车辆；-- 0:未配车  1:已绑定  2:在途 3:(到达)入库 4:超时未绑定 5:待解绑 6:解绑出库  7:所属部门不一致 9:终端未入库 10:未绑定
case when t_location.warningid=162 and vehicle_info.vehicle_status=1 then 1
     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
     else 0 end as report_status_terminal,
-- 车辆是否是离线:转速不为0，并且上传数据间隔大于5分钟  t_location.tachometer=0 and >12.5小时(720+30=750)
case when round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>5 and t_location.tachometer!=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162  then 2
     when round((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t_location.upload_time))/60)>750 and t_location.tachometer=0 and vehicle_info.vehicle_status=1 and t_location.warningid!=162 then 2 else 1 end as OffLineVehicle_Status,
now()as save_time,now()as update_time,vehicle_info.operator_id
from t_vehicle_info vehicle_info
left JOIN t_vehicleandfence vehicle_fence on vehicle_fence.VehicleVin=vehicle_info.vin
left join t_vehiclelocation t_location  on vehicle_info.vin=t_location.vinNumber
) as temp_ta where temp_table.vin=temp_ta.vin and temp_ta.report_status_fence<temp_table.report_status_fence
)


 union ALL
 select DISTINCT tvlog.VehicleVin,mechanism.Id,'2','0', '0','0',tvlog.SaveTime,tvlog.SaveTime,'84' from t_vehicleactionlog tvlog
 LEFT JOIN t_actiontypeandactiondetails tvlogdetail on tvlog.id=tvlogdetail.ActionDetailsId
 LEFT JOIN t_organizemechanism mechanism on mechanism.Id =tvlogdetail.OrangizeId
 where date(tvlog.savetime ) = curdate()
)temp_static_new
INNER JOIN t_userlevel userlevel ON temp_static_new.organization_id=userlevel.OrangizeId
INNER JOIN t_organizemechanism  mechanism on userlevel.OrangizeId=mechanism.Id
WHERE 1=1
AND SUBSTR(SUBSTRING_INDEX(userlevel.LevelTree,'_',1),1,1)=1
GROUP BY temp_static_new.organization_id