Every derived table must have its own alias

前端之家收集整理的这篇文章主要介绍了Every derived table must have its own alias前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

如下sql语句:

SELECT * FROM ( SELECT ID,URL FROM alone_action WHERE STATE = '1' ) 

在Oracle中可以执行,但在MysqL下无法通过,错误如下:

查询SELECT * FROM ( SELECT ID,URL FROM alone_action WHERE STATE = '1' ) LIMIT 0,1000 错误代码1248 Every derived table must have its own alias

Every derived table must have its own alias 意思是说 每个派生出来的表都必须有一个自己的别名,因为进行嵌套查询的时候子查询出来的的结果是作为一个派生表来进行上一级的查询的,所以子查询的结果必须要有一个别名

MysqL语句改为如下即可

SELECT * FROM ( SELECT ID,URL FROM alone_action WHERE STATE = '1' ) as t
-------------------- 分割线 --------------------

作者出现上述错误的原因不是单纯执行sql的问题,而是有个项目是Oracle的需要迁移到MysqL数据库迁移成功之后 运行报错

Caused by: com.MysqL.jdbc.exceptions.jdbc4.MysqLSyntaxErrorException: Every derived table must have its own alias

原因是因为Hibernate的dialect方言问题,将配置文件中Oracle的方言改为MysqL即可

<property name="dialect">
    org.hibernate.dialect.MysqLDialect
</property>

附:RDBMS 方言

Table Dialect
DB2 org.hibernate.dialect.DB2Dialect
DB2 AS/400 org.hibernate.dialect.DB2400Dialect
DB2 OS390 org.hibernate.dialect.DB2390Dialect
Postgresql org.hibernate.dialect.PostgresqlDialect
MysqL org.hibernate.dialect.MysqLDialect
MysqL with InnoDB org.hibernate.dialect.MysqLInnoDBDialect
MysqL with MyISAM org.hibernate.dialect.MysqLMyISAMDialect
Oracle (any version) org.hibernate.dialect.OracleDialect
Oracle 9i/10g org.hibernate.dialect.Oracle9Dialect
Sybase org.hibernate.dialect.SybaseDialect
Sybase Anywhere org.hibernate.dialect.SybaseAnywhereDialect
Microsoft sql Server org.hibernate.dialect.sqlServerDialect
SAP DB org.hibernate.dialect.SAPDBDialect
Informix org.hibernate.dialect.InformixDialect
Hypersonicsql org.hibernate.dialect.HsqlDialect
Ingres org.hibernate.dialect.IngresDialect
Progress org.hibernate.dialect.ProgressDialect
Mckoi sql org.hibernate.dialect.MckoiDialect
Interbase org.hibernate.dialect.InterbaseDialect
Pointbase org.hibernate.dialect.PointbaseDialect
FrontBase org.hibernate.dialect.FrontbaseDialect
Firebird org.hibernate.dialect.FirebirdDialect


作者:itmyhome

原文链接:https://www.f2er.com/oracle/210683.html

猜你在找的Oracle相关文章