ORA-00918:未明确定义列，在mybatis中

淩亂°似流年 2021-07-09 12:16 622阅读 0赞

今天遇到个问题，想了半天想不出原因，查阅网上的资料都说是没有指定明确的列：首先看个简单点的例子：

问题：ORA-00918:未明确定义列

eg.

select  name,  name  from a  left  join b  on a.flag = b.flag

原因：列名重复了(select后面跟了两个 name)

解决办法：

每个列名，都加上其所在表的表名作为前缀

eg.

select   a.name,  b.name  from a left  join b  on a.flag = b.flag：这里一定要注意，前面一定要加上是那个表里面的字段的列。不然报错。

再来看看我的项目里面得代码，首先是错误的语句：

<include refid="OracleDialectPrefix" />
 select
 <if test="distinct">
 distinct
 </if>
 <include refid="Base_Column_List" />, sd.ITEM_NAME AS deviceTypeName
 from CFG_DEVICE cd
 left join SYS_DICTIONARY sd on sd.CATEGORY_CODE='DEVICETYPE' and sd.ITEM_VALUE = cd.DEVICETYPE
 <if test="orderByClause != null">
 order by ${orderByClause}
 </if>
 <include refid="OracleDialectSuffix" /> <include refid="Base_Column_List" />,
 sd.ITEM_NAME AS deviceTypeName
 from CFG_DEVICE cd
 left join SYS_DICTIONARY sd on sd.CATEGORY_CODE='DEVICETYPE' and sd.ITEM_VALUE = cd.DEVICETYPE
 <if test="orderByClause != null">
 order by ${orderByClause}
 </if>
 <include refid="OracleDialectSuffix" />

改正之后的语句：

<include refid="OracleDialectPrefix" />
 select
 <if test="distinct">
 distinct
 </if>
 cd.<include refid="Base_Column_List" />, sd.ITEM_NAME AS deviceTypeName
 from CFG_DEVICE cd
 left join SYS_DICTIONARY sd on sd.CATEGORY_CODE='DEVICETYPE' and sd.ITEM_VALUE = cd.DEVICETYPE
 <if test="orderByClause != null">
 order by ${orderByClause}
 </if>
 <include refid="OracleDialectSuffix" />cd.<include refid="Base_Column_List" />,
 sd.ITEM_NAME AS deviceTypeName
 from CFG_DEVICE cd
 left join SYS_DICTIONARY sd on sd.CATEGORY_CODE='DEVICETYPE' and sd.ITEM_VALUE = cd.DEVICETYPE
 <if test="orderByClause != null">
 order by ${orderByClause}
 </if>
 <include refid="OracleDialectSuffix" />

错误原因：在mybatis里面查询的时候

这句我虽然是查询全部的，但是没有指定是哪个表里面的，所以报错，改正后

cd.<include refid="Base_Column_List" />

指定了是哪个表里面的就可以了。

sd.ITEM_NAME AS deviceTypeName

这句是指定另一个表里面的字段。