首页 新闻 会员 周边

请教：查询数据库里面的数据，报错，信息如下：

0
[已解决问题] 解决于 2016-10-08 11:02

select * from (select tmp_tb.*, row_number() over(ORDER BY b.GL_ID DESC) as rownumber from (select u.USER_ID, u.USERNAME, u.PASSWORD, u.LAST_LOGIN, u.NAME, u.IP, u.EMAIL, u.NUMBER, u.PHONE, r.ROLE_ID, r.ROLE_NAME from SYS_USER u, SYS_ROLE r where u.ROLE_ID = r.ROLE_ID and u.USERNAME != 'admin' and (r.ROLE_ID != '7' and r.PARENT_ID != '7') order by u.LAST_LOGIN desc) as tmp_tb ) as t1 where rownumber between 0 and 10

 

报错信息：org.springframework.jdbc.BadSqlGrammarException:
### Error querying database.  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 '(ORDER BY b.GL_ID DESC) as rownumber from (select  u.USER_ID,
                u.USERNAME,
        ' at line 1
### The error may exist in file [E:\workspace\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\wtpwebapps\FHMYSQL\WEB-INF\classes\mybatis\system\UserXMapper.xml]
### The error may involve UserXMapper.userlistPage-Inline
### The error occurred while setting parameters
### SQL: select * from (select tmp_tb.*, row_number() over(ORDER BY b.GL_ID DESC) as rownumber from (select  u.USER_ID,     u.USERNAME,     u.PASSWORD,     u.LAST_LOGIN,     u.NAME,     u.IP,     u.EMAIL,     u.NUMBER,     u.PHONE,     r.ROLE_ID,     r.ROLE_NAME   from SYS_USER u, SYS_ROLE r    where u.ROLE_ID = r.ROLE_ID    and u.USERNAME != 'admin'   and (r.ROLE_ID != '7' and r.PARENT_ID != '7')                   order by u.LAST_LOGIN desc) as tmp_tb ) as t1 where rownumber between 0 and 10
### 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 '(ORDER BY b.GL_ID DESC) as rownumber from (select  u.USER_ID,
                u.USERNAME,
        ' at line 1
; bad SQL grammar []; nested exception is 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 '(ORDER BY b.GL_ID DESC) as rownumber from (select  u.USER_ID,
                u.USERNAME,
        ' at line 1
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:364)
    at com.sun.proxy.$Proxy12.selectList(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:194)
    at com.fh.dao.DaoSupport.findForList(DaoSupport.java:118)
    at com.fh.service.system.user.UserService.listPdPageUser(UserService.java:84)
    at com.fh.service.system.user.UserService$$FastClassBySpringCGLIB$$9ae06eae.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)
    at com.fh.service.system.user.UserService$$EnhancerBySpringCGLIB$$fd8ca82d.listPdPageUser(<generated>)
    at com.fh.controller.system.user.UserController.listUsers(UserController.java:254)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61)
    at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
    at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
    at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
    at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
    at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)
    at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)
    at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)
    at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)
    at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383)
    at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)
    at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)

雪剑残云的主页 雪剑残云 | 菜鸟二级 | 园豆：217
提问于：2016-08-30 17:19
< >
分享
最佳答案
0

select * from (select tmp_tb.*, row_number()        ,     over(ORDER BY b.GL_ID DESC) as rownumber from (select u.USER_ID, u.USERNAME, u.PASSWORD, u.LAST_LOGIN, u.NAME, u.IP, u.EMAIL, u.NUMBER, u.PHONE, r.ROLE_ID, r.ROLE_NAME from SYS_USER u, SYS_ROLE r where u.ROLE_ID = r.ROLE_ID and u.USERNAME != 'admin' and (r.ROLE_ID != '7' and r.PARENT_ID != '7') order by u.LAST_LOGIN desc) as tmp_tb ) as t1 where rownumber between 0 and 10

 

那里缺少了个逗号

奖励园豆：5
刘宏玺 | 专家六级 |园豆：14020 | 2016-08-30 17:38

您好，逗号加了还是不对。

雪剑残云 | 园豆：217 (菜鸟二级) | 2016-08-30 17:40

@雪剑残云: 你这个是mysql啊，mysql不支持over方法。。。。

刘宏玺 | 园豆：14020 (专家六级) | 2016-08-30 17:45

@刘宏玺: 谢谢你，那应该改成什么方法，您知道吗？

雪剑残云 | 园豆：217 (菜鸟二级) | 2016-08-30 17:48

@雪剑残云: 百度一下mysql over 有很多结果的

刘宏玺 | 园豆：14020 (专家六级) | 2016-08-30 17:49
其他回答(3)
0

直接把sql拿到sql分析器里能执行么？

顾晓北 | 园豆：10844 (专家六级) | 2016-08-30 17:23

您好，在数据库里面也执行不了

支持(0) 反对(0) 雪剑残云 | 园豆：217 (菜鸟二级) | 2016-08-30 17:27

@雪剑残云: 那就是sql语句错误，检查你的sql语句呗。。。

支持(0) 反对(0) 顾晓北 | 园豆：10844 (专家六级) | 2016-08-30 17:29
0

建议你，贴代码的时候可以使用编辑器中的插入代码，

_萨瓦迪卡 | 园豆：114 (初学一级) | 2016-08-30 17:41

谢谢

支持(0) 反对(0) 雪剑残云 | 园豆：217 (菜鸟二级) | 2016-08-30 17:44
0 
SELECT
    *
FROM 
(
    SELECT
        tmp_tb.*,
        ROW_NUMBER() OVER (ORDER BY b.GL_ID DESC) AS rownumber
    FROM 
    (
        SELECT
            u.USER_ID,
            u.USERNAME,
            u.PASSWORD,
            u.LAST_LOGIN,
            u.NAME,
            u.IP,
            u.EMAIL,
            u.NUMBER,
            u.PHONE,
            r.ROLE_ID,
            r.ROLE_NAME
        FROM    SYS_USER u,
                SYS_ROLE r
        WHERE u.ROLE_ID = r.ROLE_ID
        AND u.USERNAME != 'admin'
        AND (r.ROLE_ID != '7'
        AND r.PARENT_ID != '7')
        ORDER BY u.LAST_LOGIN DESC
    ) AS tmp_tb
) AS t1
WHERE rownumber BETWEEN 0 AND 10

over 子句中的  b.GL_ID 引用错误，这个别名 b 和 字段都是不存在的

 

 

 

悦光阴 | 园豆：2251 (老鸟四级) | 2016-08-31 10:12
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。