Column 'xxx' in order clause is ambiguous

在使用mybatis时遇到错误:

Exception: org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'updateTime' in order clause is ambiguous
### The error may involve Compy.getManagerApplyList-Inline
### The error occurred while setting parameters
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'updateTime' in order clause is ambiguous
; SQL []; Column 'updateTime' in order clause is ambiguous; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'updateTime' in order clause is ambiguous
<pre>
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'updateTime' in order clause is ambiguous
### The error may involve Compy.getManagerApplyList-Inline
### The error occurred while setting parameters
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'updateTime' in order clause is ambiguous
; SQL []; Column 'updateTime' in order clause is ambiguous; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'updateTime' in order clause is ambiguous
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:973)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)

ambiguous: 模棱两可; 含糊的,不明确的; 引起歧义的; 有两种或多种意思的;

原因:如下查询

	<select id="getManagerApplyList" parameterType="java.util.HashMap" resultType="java.util.HashMap">
		select  *
		from compy_manager_apply a,compy_auth u
		where a.companyId = u.id
		AND u.status = 1
		<if test="status != null and status >-1">
			and status = #{status}
		</if>
		<if test="beginTime != null and endTime != null">
			AND FROM_UNIXTIME(a.createTime/1000,'%Y-%m-%d') >= #{beginTime} and #{endTime} >= FROM_UNIXTIME(a.createTime/1000,'%Y-%m-%d')
		</if>
		order by ${sort} ${order}
		limit #{offset},#{limit}
	</select>

进行了join 未用“别名.” 指明使用哪个表的字段

修改为:

	<select id="getManagerApplyList" parameterType="java.util.HashMap" resultType="java.util.HashMap">
		select  a.*
		from compy_manager_apply a,compy_auth u
		where a.companyId = u.id
		AND u.status = 1
		<if test="status != null and status >-1">
			and status = #{status}
		</if>
		<if test="beginTime != null and endTime != null">
			AND FROM_UNIXTIME(a.createTime/1000,'%Y-%m-%d') >= #{beginTime} and #{endTime} >= FROM_UNIXTIME(a.createTime/1000,'%Y-%m-%d')
		</if>
		order by ${sort} ${order}
		limit #{offset},#{limit}
	</select>

因为次处我不需要compy_auth表的数据

(全文完)

(转载本站文章请注明作者和出处 Column 'xxx' in order clause is ambiguous