MySql case when then 同表多连接 时间判断 sql语句

SELECT 
info.uuid AS carUuid,
info.vehicle_plate_number AS carNumber,
obd.device_id AS obdDeviceId,
//null替换为0
COALESCE(obd.speed,0) AS obdSpeed,
obd.update_time AS obdTime,
obd.position AS obdAddress,
obd.bd_lng AS obdLng,
obd.bd_lat AS obdLat,
zgps.device_id AS zgpsDeviceId,
COALESCE(zgps.speed,0) AS zgpsSpeed,
zgps.update_time AS zgpsTime,
zgps.position AS zgpsAddress,
zgps.bd_lng AS zgpsLng,
zgps.bd_lat AS zgpsLat,
bgps.device_id AS bgpsDeviceId,
COALESCE(bgps.speed,0) AS bgpsSpeed,
bgps.update_time AS bgpsTime,
bgps.position AS bgpsAddress,
bgps.bd_lng AS bgpsLng,
bgps.bd_lat AS bgpsLat,
//条件判断 
CASE WHEN obd.car_status = '0' THEN '离线'
		 WHEN obd.car_status = '1' AND obd.speed > 120 THEN '超速'
		 WHEN obd.car_status = '1' AND obd.speed = 0 THEN '静止'
		 ELSE '运动'
		 END obdStatus,
CASE WHEN zgps.car_status = '0' THEN '离线'
     WHEN zgps.car_status = '1' AND zgps.speed > 120 THEN '超速'
		 WHEN zgps.car_status = '1' AND zgps.speed = 0 THEN '静止'
		 ELSE '运动'
		 END zgpsStatus,
CASE WHEN bgps.car_status = '0' THEN '离线'
		 WHEN bgps.car_status = '1' AND bgps.speed > 120 THEN '超速'
		 WHEN bgps.car_status = '1' AND bgps.speed = 0 THEN '静止'
		 ELSE '运动'
		 END bgpsStatus
FROM `longrent-rent`.tb_vehicle_info info LEFT JOIN `longrent-risk`.tb_obd_device obd ON info.uuid = obd.car_uuid
//同一张表不同条件
LEFT JOIN `longrent-risk`.tb_gps_device zgps ON info.uuid = zgps.car_uuid AND zgps.gps_type = 1
LEFT JOIN `longrent-risk`.tb_gps_device bgps ON info.uuid = bgps.car_uuid AND bgps.gps_type =0
WHERE 
info.vehicle_status = 0 
and info.del = 0 
//curdate()获取当前年月日 
AND DATE_FORMAT(obd.update_time, '%Y-%m-%d') = CURDATE()
AND obd.data_status = 1;
  • 2
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值