MySQL：GROUP BY with HAVING返回空集，但不使用GROUP BY [duplicate]

Question

This question already has an answer here:

• SELECT From MySQL View With HAVING Clause Returns Empty Result Set 2 answers

I have a table (cargo_route) that consists of following fields:

id,
truck_id,
lat,
lon,
radius

it's a shipping route where each row is each stop with lat,lon coordinates for a truck.

I need to calculate a distance between each stop and my (any input) point (lat lon) and bring results if the distance is less than specified radius (for that stop). (Basically search whether the city is on the route or too far away from any stop on the route)

HERE is my query (which works fine)

SELECT
`truck_id`,
`radius`,
(
    3959 * acos(
        cos(radians(39.9611755)) * cos(radians(cargo_route.lat)) * cos(
            radians(cargo_route.lon) - radians(- 82.9987942)
        ) + sin(radians(39.9611755)) * sin(radians(cargo_route.lat))
    )
) AS distance FROM
(`cargo_route`) HAVING `distance` < `radius`

The code works.... BUT, it brings me several rows with the same truck_id, (basically every single stop that's close enough to the searched point)

When I try to "GROUP BY truck_id" to only get ids of trucks that pass through my point, I get an empty set :(

I could resolve this within PHP logic but I'd rather get SQL to return me what I really need than iterate through an array.

I did research and learned that GROUP BY executes first and HAVING ends up "not having" distance < radius because group by grabs the first row (hence HAVING producing the empty set). I also tried ORDER B but it has no effect.

HEre is the query that returns EMPTY SET

    SELECT
`truck_id`,
`radius`,
(
    3959 * acos(
        cos(radians(39.9611755)) * cos(radians(cargo_route.lat)) * cos(
            radians(cargo_route.lon) - radians(- 82.9987942)
        ) + sin(radians(39.9611755)) * sin(radians(cargo_route.lat))
    )
) AS distance FROM 
(`cargo_route`)  GROUP BY truck_id HAVING `distance` < `radius` 

QUESTION: IS there way to execute HAVING and then group by after? Perhaps a slightly different syntax?

I tried reordering HAVING and GROUP BY but it throws me an error. ALso, I cannot use WHERE because 'distance' is an aggregated column.

************EDIT _ SOLVED *******************

Canno answer my own question:

Guys, actually found an answer here: SELECT From MySQL View With HAVING Clause Returns Empty Result Set

Basically, put SELECT * FROM (my query) S WHERE distance < radius GROUP BY truck_id

</div>

Answer 1

It might work if you move distance evaluation directly to WHERE clause. You can also wrap everything(without GROUP BY) into subquery and in GROUP BY in parent query.