内连接mysql的sql错误

Question

String sql = "set @row \\:=-1; SELECT ue.id, ue.Latitude, ue.Longitude, ue.Serving_Cell, ue.RSCP 
FROM ue INNER JOIN(
     SELECT id 
     from (
          SELECT @row \\:=@row+1 as rownum, id 
          from (
          select id from ue order by id) as sorted
                )as ranked  
      where rownum %20=0) as subset 
      on subset.id = ue.id 
where Operator like :operator ";

Query query = getSessionFactory().getCurrentSession().createSQLQuery(sql);

query.setParameter("operator", "%" + operator + "%");

return query.list();

请帮助我找到我的SQL错误控制台呈现给我:Servlet.service: Servlet.service() for servlet dispatcher在上下文中与路径/SpringSecurity抛出异常请求处理失败；嵌套异常是org.hibernate.exception.SQLGrammarException：无法从根本原因提取ResultSet，您的sql语法中有错误；检查与MySQL服务器版本对应的手册，以获得在第1行 'SELECT ue.id、ue.Latitude、ue.Longitude、ue.Serving_Cell、ue.RSCP FROM ue INNER‘附近使用的正确语法。

Answer 1

您尝试一次执行两个查询。一次只能执行一个。

SELECT ue.id, ue.Latitude, ue.Longitude, ue.Serving_Cell, ue.RSCP 
FROM ue INNER JOIN(
     SELECT id 
     from (
          SELECT @row \\:=@row+1 as rownum, id 
          from (
          select id, @row 
          from ue, (select @row := -1) r
          order by id) as sorted
                )as ranked  
      where rownum %20=0) as subset 
      on subset.id = ue.id 
where Operator like :operator

但是，您可以使用子查询来初始化这样的变量

(select @row := -1) r