mysql竖排显示列名_使用列值作为列名mysql

I am trying to get all users in 5 KM radius. here is my table structure

id| location

-------------

1| 26.851791,75.781810

2| 26.860729,75.7633127

3| 34.057811,-84.239125

I write a query to separate the location according to lat and long

SELECT SUBSTRING_INDEX( location, ',', 1 ) AS lat, SUBSTRING_INDEX( location, ',', -1 ) AS lng

FROM `users_test`

LIMIT 0 , 30

Which work fine and I got results as following

then I write a query to get all the users who are in 5KM Radius according to the following blog.

SELECT *,SUBSTRING_INDEX( location, ',', 1 ) AS lat, SUBSTRING_INDEX( location, ',', -1 ) AS lng,(((acos(sin(("26.851791"*pi()/180)) * sin((`lat`*pi()/180))+cos(("26.851791"*pi()/180)) * cos((`lat`*pi()/180)) * cos((("75.781810"- `lng`)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance

FROM `users_test`

WHERE distance >= "5"

but I am getting following error.

#1054 - Unknown column 'lat' in 'field list'

can anyone tell me where I am wrong.

解决方案

You can't refer to a column alias in the same SELECT clause. You need to move it into a subquery. And you can't refer to an alias in the WHERE clause of the same SELECT, you need to use HAVING.

SELECT *, (((acos(sin(("26.851791"*pi()/180)) * sin((`lat`*pi()/180))+cos(("26.851791"*pi()/180)) * cos((`lat`*pi()/180)) * cos((("75.781810"- `lng`)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance

FROM (SELECT *,

SUBSTRING_INDEX( location, ',', 1 ) AS lat,

SUBSTRING_INDEX( location, ',', -1 ) AS lng

FROM users_test) x

HAVING distance > 5

I strongly suggest you fix your table design to put the latitude and longitude in their own columns, instead of having to split on comma every time.