包含 MyBatis SQL 查询的不明确列

Question

我在下面的查询中遇到了错误。你能帮帮我吗？

该错误是由不明确的列引起的。我有一个想法，我不确定。

我认为错误是这里的 WorkUID 列(模棱两可(：

<isNotEmpty property="uids">
AND WorkUID IN
<iterate  property="uids" open="(" close=")" conjunction=",">
#uids[]#
</iterate>
</isNotEmpty>

这是错误：

Cause: SqlMapClient operation; SQL [];   
--- The error occurred while applying a parameter map.  
--- Check the getData-InlineParameterMap.  
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: Ambiguous column name 'WorkUID'.; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred while applying a parameter map.  
--- Check the getData-InlineParameterMap.  
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: Ambiguous column name 'WorkUID'.;
net.extraction.etl.exception.DAOException: Getting work UID by release datetime.
Cause: org.springframework.dao.TransientDataAccessResourceException: SqlMapClient operation; SQL [];   
--- The error occurred while applying a parameter map.  
--- Check the getData-InlineParameterMap.

下面是查询：

<select id="getData" resultClass="java.lang.Integer" parameterClass="java.util.Map" >
<![CDATA[
SELECT xrn.WorkUID
FROM
(SELECT uow.WorkUID,
ROW_NUMBER() OVER(PARTITION BY wsh.WorkUID ORDER BY wsh.DT DESC) AS rnk
FROM UnitWork uow
INNER JOIN SigHist sh ON  sh.SigUID = uow.SigUID
INNER JOIN DataCell d ON d.WorkUID = uow.WorkUID
INNER JOIN WorkStatHist wsh ON (wsh.WorkUID = uow.WorkUID
AND wsh.WorkUID = d.WorkUID
AND 8 =
(SELECT TOP 1 StatusUID
FROM WorkStatHist(NOLOCK)
WHERE WorkUID = uow.workUID
ORDER BY DT DESC))
WHERE (uow.ReleasedDT > #fromDate#)
OR (sh.UpdatedDT > #fromDate#)
AND d.EffectiveTo >= GETDATE() + 1
]]>
<dynamic>
<isNotEmpty property="toDate">
<![CDATA[
AND d.EffectiveTo < #toDate#
]]>
</isNotEmpty>
<isNotEmpty property="uids">
AND WorkUID IN
<iterate  property="uids" open="(" close=")" conjunction=",">
#uids[]#
</iterate>
</isNotEmpty>
</dynamic>
<![CDATA[
) xrn
WHERE xrn.rnk=1
]]>
</select>

Answer 1

我根本不熟悉MyBatis，所以我不能说这部分代码，但似乎您的问题出在以下部分：

<isNotEmpty property="uids">
AND WorkUID IN
<iterate  property="uids" open="(" close=")" conjunction=",">
#uids[]#
</iterate>
</isNotEmpty>

尤其是AND WorkUID IN部分。

查询中有多个表WorkUID为列，因此它不知道要从哪个表中提取它。

看到它被用作每个表的JOIN约束，我们告诉它查看哪个表并不特别重要。

将其更改为以下内容应清除该错误：

<isNotEmpty property="uids">
AND uow.WorkUID IN
<iterate  property="uids" open="(" close=")" conjunction=",">
#uids[]#
</iterate>
</isNotEmpty>