mysql - 同一查询的 where 子句中的别名值

Question

我想得到我们在同一个查询中获取的 distance >=distance_filter 的结果

SELECT SQL_CALC_FOUND_ROWS null as row, distance_filter, j.lat, j.long, ROUND((((acos(sin((28.53551600*pi()/180)) * sin((j.lat*pi()/180)) + cos((28.53551600*pi()/180)) * cos((j.lat*pi()/180)) * cos(((77.39102600 - j.long) * pi()/180))))*180/pi())*60*1.1515*1.609344 )) as distance, j.job_id, j.brand, j.location, j.model, j.creation_time
FROM `mb_job` as `j`
JOIN `mb_job_issue` as `ji` ON `j`.`job_id`=`ji`.`job_id`
WHERE `j`.`creation_time` >= '2017-05-18 09:39:43'
AND `j`.`status` = '1'
AND `j`.`job_id` NOT IN('')
GROUP BY `j`.`job_id`
HAVING `distance` <= 'DISTANCE_FILTER'
ORDER BY `j`.`job_id` DESC
 LIMIT 10

提前感谢任何指导

Answer 1

您只需要将 having 子句放在单引号内。这个查询在我这边运行良好

SELECT SQL_CALC_FOUND_ROWS null as row, distance_filter, j.lat, j.long, ROUND((((acos(sin((28.53551600*pi()/180)) * sin((j.lat*pi()/180)) + cos((28.53551600*pi()/180)) * cos((j.lat*pi()/180)) * cos(((77.39102600 - j.long) * pi()/180))))*180/pi())*60*1.1515*1.609344 )) as distance, j.job_id, j.brand, j.location, j.model, j.creation_time
FROM `mb_job` as `j`
JOIN `mb_job_issue` as `ji` ON `j`.`job_id`=`ji`.`job_id`
WHERE `j`.`creation_time` >= '2017-05-18 09:39:43'
AND `j`.`status` = '1'
AND `j`.`job_id` NOT IN('')
GROUP BY `j`.`job_id`
HAVING 'distance <= distance_filter'
ORDER BY `j`.`job_id` DESC
 LIMIT 10

希望对你有帮助