首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >使用内部联接时的SQL set变量

问使用内部联接时的SQL set变量
EN

Stack Overflow用户
提问于 2013-12-24 10:41:13
回答 3查看 1.4K关注 0票数 1

我有两张桌子:

代码语言:javascript
复制
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是用户的经度和纬度。

代码语言:javascript
复制
    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)

代码语言:javascript
复制
    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";

我得到了以下错误:

代码语言:javascript
复制
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。

EN

回答 3

Stack Overflow用户

发布于 2013-12-24 10:44:33

您混淆了内部联接和select语句。试试这个:

代码语言:javascript
复制
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";
票数 0
EN

Stack Overflow用户

发布于 2013-12-24 10:54:08

您的SQL语句已中断。

它需要看起来像这样

代码语言:javascript
复制
SELECT *, 
       [domaths] AS distance 
FROM   table 
       INNER JOIN other_table 
               ON id = id 
票数 0
EN

Stack Overflow用户

发布于 2013-12-24 11:44:41

你试过这个吗,

代码语言:javascript
复制
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; 
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/20759420

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档