Every derived table must have its own alias

最新推荐文章于 2024-02-24 23:02:33 发布
最新推荐文章于 2024-02-24 23:02:33 发布
阅读量1.9w 收藏 1
点赞数 2
分类专栏： MySQL 文章标签： MySQL Select SQL 临时表 别名
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/testcs_dn/article/details/75529946
版权

完整错误信息如下：

Every derived table must have its own alias


三月 28, 2017 10:20:46 上午 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet [SpringMVC] in context with path [/yuyi] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias
### The error may exist in file [/Users/aven/software/apachetomcat8041/webapps/yuyi/WEB-INF/classes/com/wanyu/yuyi/mapping/TemperatureMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select * from (select a.* from temperature a, (select date(create_time) create_time,max(create_time) max_datetime from temperature where humeuser_id = ? group by date(create_time)) b where a.create_time=b.max_datetime and date(a.create_time)=b.create_time AND a.humeuser_id = ? GROUP BY a.create_time ORDER BY a.create_time DESC Limit 7) ORDER BY a.create_time ASC
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias] with root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
	at com.mysql.jdbc.Util.getInstance(Util.java:384)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1307)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62)
	at com.sun.proxy.$Proxy30.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:59)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
	at com.sun.proxy.$Proxy13.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198)
	at com.wanyu.yuyi.dao.TemperatureDao.findByWeekSelect(TemperatureDao.java:42)
	at com.wanyu.yuyi.service.TemperatureManager.findByWeekSelect(TemperatureManager.java:44)
	at com.wanyu.yuyi.controller.TemperatureController.findList(TemperatureController.java:66)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	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:690)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:945)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:876)
	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.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:108)
	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:94)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:509)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1104)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1524)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1480)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)

这主要是SQL语句的语法错误：

select * from (select a.* from temperature a, (select date(create_time) create_time,max(create_time) max_datetime from temperature where humeuser_id = ? group by date(create_time)) b where a.create_time=b.max_datetime and date(a.create_time)=b.create_time AND a.humeuser_id = ? GROUP BY a.create_time ORDER BY a.create_time DESC Limit 7) ORDER BY a.create_time ASC
select * from (select a.* from temperatur)   别名 where id=1

Select 嵌套使用临时表时，需要为临时表指定别名。


微wx笑
关注
  • 2
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql错误：Every derived table must have its own alias
08-14
NULL 博文链接：https://happyfling.iteye.com/blog/2313240
mysql派生表（Derived Table）简单用法实例解析
09-08
主要介绍了mysql派生表（Derived Table）简单用法,结合实例形式分析了mysql派生表的原理、简单使用方法及操作注意事项,需要的朋友可以参考下
1248 - Every derived table must have its own alias
最新发布
Zm6Cc的博客
02-24 728
当执行 SQL 查询时，数据库管理系统（DBMS）会按照特定的顺序处理查询语句中的不同部分。理解 SQL 执行顺序对于编写复杂查询和调试 SQL 查询非常重要。这些步骤是在标准 SQL 执行顺序中列出的，但有些 DBMS 可能会对其进行优化或调整。理解这些执行步骤可以帮助你编写更有效和准确的 SQL 查询，并且能够更好地调试和理解查询结果。该问题是子查询内的列名无法在外部查询中直接引用，使用别名来为子查询结果集命名，然后在外部查询中引用该别名。在这个修正后的查询中，我们为子查询结果集命名为。
Mysql错误Every derived table must have its own alias解决方法
09-09
主要介绍了Mysql错误Every derived table must have its own alias解决方法,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
mysql “ Every derived table must have its own alias”出现错误解决办法
12-16
mysql  Every derived table must have its own alias错误解决办法 Every derived table must have its own alias 这句话的意思是说每个派生出来的表都必须有一个自己的别名 一般在多表查询时，会出现此错误。 因为，进行嵌套查询的时候子查询出来的的结果是作为一个派生表来进行上一级的查询的，所以子查询的结果必须要有一个别名 把MySQL语句改成：select count(*) from (select * from ……) as total; 问题就解决了，虽然只加了一个没有任何作用的别名total，但这个
Mysql报错：Every derived table must have its own alias
m0_52453038的博客
07-25 478
Every derived table must have its own alias
Every derived table must have its own alias（sql语句错误解决方法）
热门推荐
KING
10-12 46万+
1. 在做多表查询，或者查询的时候产生新的表的时候会出现这个错误：Every derived table must have its own alias（每一个派生出来的表都必须有一个自己的别名）。 eg： delete from stock where (org_id,material_id,state) in (SELECT * from (select org_id,ma...
解决数据库报错：Every derived table must have its own alias
weixin_50730022的博客
12-25 2318
Every derived table must have its own alias报错信息解决方案
sql语句错误解决方法之Every derived table must have its own alias
oyu~的博客
10-04 1615
这段命令会先查 score 表，然后生成一张新的临时表，如果这个临时表没有命名，就会导致 1248 错误。我们只需要加上 「as 临时表别名」即可修复错误，as可省略。sql如果写成这样就会报错: Every derived table must have its own alias.mysql规定子查询这个表必须要有别名,至于别名是什么你可以随便写,加在子查询的后面再执行就不会报错了。意思是「每一个派生出来的表都必须有一个自己的别名」今天在练习MySQL的时候发生了这个错误。
MySQL中每个派生表都要有自己的别名（Every derived table must have its own alias）解决
yytasty的博客
06-16 1442
1.今天操作测试数据库的时候报了个错 “Every derived table must have its own alias” 搜索字段为 UPDATE sku SET updatetime=now(),groupid = (SELECT groupid FROM (SELECT MAX(groupid) AS groupid FROM sku) )+1 WHERE skuid in (''); 2.告诉我们派生表都要有自己的别名，然后我们都知道"派生表"就是由搜索数据库表得出来的临时表 即由：SE
MySQL「 Every derived table must have its own alias」1248 错误修复法
蒋川@卡拉云
12-03 3275
在写带有子查询或者在查询时产生临时表的查询时，可能会出现这个错误：ERROR 1248 (42000): Every derived table must have its own alias意思是「每一个派生出来的表都必须给它命名一个自己的别名」
linux mysql 表名 大小写敏感_Linux下Mysql的表名大小写敏感引起数据库操作失败
weixin_34660766的博客
02-01 245
org.springframework.jdbc.BadSqlGrammarException:### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'gonggao.BC_NOTICE_ATTACHMENT' doesn't exist### Th...
【错误记录】mysql-Every derived table must have its own alias
吉吉国王的博客
04-22 45
【错误记录】mysql-Every derived table must have its own alias select * from (select * from employees order by hire_date desc limit 3） order by hire_date limit 1 错误原因：每个派生出来的表必须有一个自己的别名 错误解决：通过as字段添加别名即可 select * from (select * from employees order by hire_dat
MyBatis Plus分页插件使用问题
zonelza3的专栏
03-23 2351
MyBatis Plus分页插件使用问题 一、问题描述 项目中遇到SQL报错抛异常，报错内容如下。 [Code: 1248, SQL State: 42000] Every derived table must have its own alias 查看报错的SQL如下: SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM ROW_ID FROM ...... 很明显，ROWNUM是属于Oracle的语法，而把出错的语句放到Oracle环境下执行是没有问题的。
Java分页查询报错 Every derived table must have its own alias
yiyuzz的博客
03-17 2988
今天在项目中增加了一个简单的分页查询数据的接口，出现了Every derived table must have its own alias这个报错，看起来是个别名问题，但是sql很规范，该有的别名也都有，而且不加分页是能直接查询成功的，所以基本可以确定是分页插件的问题。 仔细观察了sql查询的日志发现，这个ROWNUM和ROW_ID很奇怪，这两个是Oracle分页的时候用到的，mysql应该是使用limit来进行分页。 由于是微服务项目，所以mybatisplus的分页配置在公共模块，果然是配
MySQLSyntaxErrorException: Every derived table must have its own alias
Colourful.的博客
03-18 354
每个派生出来的表都必须有一个自己的别名 因为，进行嵌套查询的时候子查询出来的的结果是作为一个派生表来进行上一级的查询的，所以子查询的结果必须要有一个别名 把MySQL语句改成：select count(*) from (select * from ……) as total; 问题就解决了，虽然只加了一个没有任何作用的别名total，但这个别名是必须的 错误子查询： SELECT count( name ) AS name_num FROM (

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值