where clause is ambiguous

2019独角兽企业重金招聘Python工程师标准>>>

1. 查询数据库时，出现where clause is ambiguous
2. 这个错误in where clause is ambiguous 多半是因为多表查询的时候几个表中同时出现了某个相同的列名，而在查询条件WHERE后面又没有指定是那个表，而引起的，又或者是查询结果里面有两个相同的列名，而没有指定是哪个表。
3. 使用的时候可以这样，mysql查询前面加表名可避免出现这个错误 。

<select id="findByUserId" resultType="Orders">
		SELECT distinct
			<include refid="ordersColumns"/>
			,s.shop_name AS "store.shopName"
		FROM map_orders a
		left outer join map_orders_goods og on og.map_orders_id=a.id
		left outer join map_store_goods g on g.id=og.map_store_goods_id
		<include refid="ordersJoins"/>
		<where>
			<if test="null != user and user.id != null and user.id != ''">
				AND a.user_id = #{user.id}
			</if>
			<if test="orderStatus != null and orderStatus != ''">
				AND a.order_status = #{orderStatus}
			</if>
			<if test="storeGoods.name != null and storeGoods.name != ''">
				AND (storeGoods.name LIKE 
					<if test="dbName == 'oracle'">'%'||#{storeGoods.name}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{storeGoods.name}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{storeGoods.name},'%')</if> 
				or a.id LIKE 
					<if test="dbName == 'oracle'">'%'||#{id}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{id}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{id},'%'))</if>
			</if>
			AND a.del_flag='0'
		</where>
		ORDER BY a.order_status,a.create_time
	</select>

 

转载于:https://my.oschina.net/discussjava/blog/822585