SELECT DISTINCT cr.id,
IF(
cr.cur_local IS NULL,
cr.end_local,
cr.cur_local
) AS end_local,
cr.route_status,
cra.car_info_id,
si.user_name AS name,
ci.car_number,
ci.car_used,
si.phone_number AS driver_phone,
ci.use_type,
IF(
cr.cur_local_time IS NULL,
cr.end_time,
cr.cur_local_time
) AS end_time,
cr.is_gps_hardware AS is_device
FROM car_route cr
LEFT JOIN car_route_apply cra ON cr.id = cra.route_id
LEFT JOIN car_info ci ON ci.id = cra.car_info_id
LEFT JOIN zt_sys_user_info si ON si.id = cr.user_id
LEFT JOIN zt_sys_staff_info ss ON ss.user_id = cr.user_id
WHERE ss.company_id = '5b14469fd5564f04a0a2baed31d8d7c6'
AND cr.create_time IN (SELECT MAX(b.create_time)
FROM car_route b
LEFT JOIN zt_sys_staff_info c ON c.user_id = b.user_id
WHERE b.user_id IS NOT NULL
AND b.user_id <> ''
AND b.route_status IN (2, 3, 4)
AND b.route_type IN (1, 2)
AND c.company_id = '5b14469fd5564f04a0a2baed31d8d7c6'
AND (
ST_Distance(
ST_GeomFromText(
CONCAT(
'POINT(',
SUBSTRING_INDEX(cur_local, ',', - 1),
' ',
SUBSTRING_INDEX(cur_local, ',', 1),
')'
)
),
ST_GeomFromText(
CONCAT(
'POINT(',
SUBSTRING_INDEX(
'39.915,116.404',
',', - 1
),
' ',
SUBSTRING_INDEX(
'39.915,116.404',
',',
1
),
')'
)
)
) < 37417
OR
ST_Distance(
ST_GeomFromText(
CONCAT(
'POINT(',
SUBSTRING_INDEX(end_local, ',', - 1),
' ',
SUBSTRING_INDEX(end_local, ',', 1),
')'
)
),
ST_GeomFromText(
CONCAT(
'POINT(',
SUBSTRING_INDEX(
'39.915,116.404',
',', - 1
),
' ',
SUBSTRING_INDEX(
'39.915,116.404',
',',
1
),
')'
)
)
) < 37417
)
GROUP BY b.user_id)
帮我优化这段sql
1290
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
