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;
MySql case when then 同表多连接 时间判断 sql语句
最新推荐文章于 2024-05-06 23:01:53 发布