我有两张桌子:
1. Coupons with columns {id, business_id, name}
2. Businesses with columns {business_id, name, latitude, longitude}一般来说,它是一个优惠券管理网站,每个企业都可以提供优惠券。
Coupons.business_id是优惠券链接到的Business.business_id。
我想得到所有的优惠券,足够接近(20公里)的用户位置。
下面的SQL代码为我提供了这个范围内的businesses:
注: lat和lng是用户的经度和纬度。
String query = "SELECT *,(((acos(sin((" + lat + "*pi()/180)) * "
+" sin((`Latitude`*pi()/180))+cos((" + lat + "*pi()/180)) * "
+ " cos((`Latitude`*pi()/180)) * cos(((" + lng + "- `Longitude`)* "
+ " pi()/180))))*180/pi())*60*1.1515 " +
") as distance " +
"FROM `Businesses` HAVING distance < 20 ORDER BY distance";我试着使用下面的代码来获取密密麻麻的优惠券:(称之为second query)
String query = "SELECT * FROM COUPONS inner join Businesses on Coupons.business_id = Businesses.business_id"
+ " (((acos(sin((" + lat + "*pi()/180)) * "
+" sin((`Businesses.latitude`*pi()/180))+cos((" + lat + "*pi()/180)) * "
+ " cos((`Businesses.latitude`*pi()/180)) * cos(((" + lng + "- `Businesses.longitude`)* "
+ " pi()/180))))*180/pi())*60*1.1515 " +
") as distance HAVING distance < 20 ORDER BY distance";我得到了以下错误:
Hibernate: SELECT * FROM COUPONS inner join Businesses on Coupons.business_id = Businesses.business_id (((acos(sin((33.207933*pi()/180)) * sin((`Businesses.latitude`*pi()/180))+cos((33.207933*pi()/180)) * cos((`Businesses.latitude`*pi()/180)) * cos(((35.570246- `Businesses.longitude`)* pi()/180))))*180/pi())*60*1.1515 ) as distance HAVING distance < 20 ORDER BY distance
Dec 24, 2013 12:36:31 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [jsp] in context with path [/InternetProject] threw exception [org.hibernate.exception.SQLGrammarException: could not execute query] with root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'as distance HAVING distance < 20 ORDER BY distance' at line 1我的第二个查询中有什么问题,以及如何修复它?提前谢谢..。使用mySQL。
发布于 2013-12-24 10:44:33
您混淆了内部联接和select语句。试试这个:
String query = "SELECT *, (((acos(sin((" + lat + "*pi()/180)) * "
+" sin((`Businesses.latitude`*pi()/180))+cos((" + lat + "*pi()/180)) * "
+ " cos((`Businesses.latitude`*pi()/180)) * cos(((" + lng + "- `Businesses.longitude`)* "
+ " pi()/180))))*180/pi())*60*1.1515 " +
") as distance FROM COUPONS inner join Businesses on Coupons.business_id = Businesses.business_id HAVING distance < 20 ORDER BY distance";发布于 2013-12-24 10:54:08
您的SQL语句已中断。
它需要看起来像这样
SELECT *,
[domaths] AS distance
FROM table
INNER JOIN other_table
ON id = id 发布于 2013-12-24 11:44:41
你试过这个吗,
SELECT c.business_id,
c.agent_name,
bus.mybusinessid,
bus.latitude,
bus.longitude
FROM coupons c
INNER JOIN (SELECT business_id,
Count(*) AS mybusinessid,
FROM businesses
GROUP BY business_id) bus
ON bus.business_id = c.business_id; https://stackoverflow.com/questions/20759420
复制相似问题