嗨,我真的很难让下面的sql查询正常工作,如果我把它混合在一起,我提前道歉,但我仍在学习sql的全部高级领域。
这是我的密码。。。
"SELECT *,(((acos(sin((".$latitude."*pi()/180)) *
sin((`latitude`*pi()/180))+cos((".$latitude."*pi()/180)) *
cos((`latitude`*pi()/180)) * cos(((".$longitude."- `longitude`)*pi()/180))))
*180/pi())*60*1.1515) as distance
FROM `locations` l HAVING distance <= '".$distance."' JOIN
(SELECT * users) u
ON (l.id = u.basic_location)
WHERE u.id != A $AND2
ORDER BY distance ASC"
我一直收到以下错误消息。。。
You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
'JOIN (SELECT * users) u ON (l.id = u.basic_location) WHERE u.id' at line 1
我尝试了很多组合,但我被难住了,正在寻求帮助?
JOIN
应紧跟在FROM tablename
或另一个JOIN
之后- 在这种情况下,应该使用
WHERE
,而不是HAVING
- 您应该
JOIN users u
,而不是子查询(在这种情况下,这毫无意义)
试试这个
"SELECT *,
(((( SELECT
acos(
sin((".$latitude."*pi()/180)) *
sin((`latitude`*pi()/180))+
cos((".$latitude."*pi()/180)) *
cos((`latitude`*pi()/180)) *
cos(((".$longitude."- `longitude`)*pi()/180))
))*180/pi())*60*1.1515) as distance
FROM `locations` l )X
JOIN users u
ON (X.id = u.basic_location)
WHERE u.id != A $AND2
AND distance <= '".$distance."'
ORDER BY distance ASC"
更改
1) HAVING distance <=
需要更改为WHERE distance <=
。请注意,只有当您具有SUM、COUNT等聚合函数时,才使用HAVING子句进行筛选。对于任何正常的筛选操作,请使用WHERE子句
2) Join语法为:
SELECT t1.*
FROM Table1 t1
JOIN Table2 t2
ON t1.Id = t2.Id
Where t1.<Some condition>
AND t2.<some Condition>
而不是
SELECT t1.*
FROM Table1 t1 Where t1.<Some condition>
JOIN Table2 t2
ON t1.Id = t2.Id
Where t2.<some Condition>
如果你需要在加入前从第一张表中筛选,那么就喜欢
SELECT X.*,Y.*
FROM (SELECT * FROM Table1 t1 Where t1.<Some condition>) X
JOIN (SELECT * FROM Table2 t2) Y
ON X.Id = Y.Id
Where Y.<some Condition>
希望你能理解。
select * from
(SELECT *,
(((acos(sin((".$latitude."*pi()/180)) *
sin((`latitude`*pi()/180))+
cos((".$latitude."*pi()/180)) *
cos((`latitude`*pi()/180)) *
cos(((".$longitude."- `longitude`)*pi()/180))))*
180/pi())*60*1.1515) as distance
FROM `locations` l INNER JOIN
`users` u
ON l.id = u.basic_location
WHERE u.id != A $AND2) derived_table dt
where dt.distance <= '".$distance."'
ORDER BY dt.distance ASC