开发者

SQL Query MySQL

I'm getting below error when I perform SQL query below. What do you think is wrong?

#1054 - Unknown column 'distance' in 'where clause'
SELECT longitude, latitude, firstname, surname, profile_pic, facebook_id, 6371 * ACos( Cos( RADIANS( users.latitude ) ) * Cos( RADIANS(开发者_高级运维 23 ) ) * Cos( RADIANS( 23 ) - RADIANS( users.longitude ) ) + Sin( RADIANS( users.latitude ) ) * Sin( RADIANS( 23 ) ) ) AS distance
FROM users
WHERE distance >=1000
ORDER BY distance
LIMIT 20 


Your where clause is evaluated before the alias.

SELECT longitude, latitude, firstname, surname, profile_pic, facebook_id, distance 
FROM 
  (SELECT longitude, latitude, firstname, surname, profile_pic, facebook_id, 6371 * ACos( Cos( RADIANS( users.latitude ) ) * Cos( RADIANS( 23 ) ) * Cos( RADIANS( 23 ) - RADIANS( users.longitude ) ) + Sin( RADIANS( users.latitude ) ) * Sin( RADIANS( 23 ) ) ) AS distance
   FROM users
  ) as u1
  WHERE distance >=1000
  ORDER BY distance
  LIMIT 20


Instead of WHERE clause, use HAVING clause. The alias distance does not exist in WHERE clause.

SELECT longitude, latitude, firstname, surname, profile_pic, facebook_id, 6371 * ACos( Cos( RADIANS( users.latitude ) ) * Cos( RADIANS( 23 ) ) * Cos( RADIANS( 23 ) - RADIANS( users.longitude ) ) + Sin( RADIANS( users.latitude ) ) * Sin( RADIANS( 23 ) ) ) AS distance
FROM users
HAVING distance >=1000
ORDER BY distance
LIMIT 20 


You can use inner query like...

SELECT * 
FROM 
  (
   SELECT longitude, latitude, firstname, surname, profile_pic, facebook_id, 6371 * ACos( Cos( RADIANS( users.latitude ) ) * Cos( RADIANS( 23 ) ) * Cos( RADIANS( 23 ) - RADIANS( users.longitude ) ) + Sin( RADIANS( users.latitude ) ) * Sin( RADIANS( 23 ) ) ) AS distance
   FROM Users
  ) as u1
WHERE distance >=1000
ORDER BY distance
LIMIT 20
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜