多次为 'PAGE_TABLE_ALIAS' 指定了列 'XXX' 错误解决

在使用mybatis 级联一对多查询时遇到一个错误：

uncategorized SQLException; SQL state [S0001]; error code [8156]; 多次为 'PAGE_TABLE_ALIAS' 指定了列 'COID'。; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 多次为 'PAGE_TABLE_ALIAS' 指定了列 'COID'。

原因是在A表有一个字段coid, B表也有一个coid,在使用分页时导致俩个重复的coid无法区分造成的，我们看一下打印出的sql:

SELECT TOP 1 * FROM 
(SELECT ROW_NUMBER() OVER (ORDER BY CR_Style) PAGE_ROW_NUMBER, * FROM 
   (SELECT cc.*, cm.* FROM [wx].[dbo].[CM_Company] cc LEFT JOIN [wx].[dbo].[CM_Matcher] cm ON cc.coid = cm.coid WHERE cc.CO_Name LIKE '%武汉%') 
   AS PAGE_TABLE_ALIAS )
 AS PAGE_TABLE_ALIAS WHERE PAGE_ROW_NUMBER > 1 ORDER BY PAGE_ROW_NUMBER;

可以看到 虽然我们对俩张表取了别名， 但在分页时俩张表合并成了*， 所以有字段重复时导致无法识别报错。

解决办法：对其中重复的字段取别名
修改之前的sql及resultMap:

 <resultMap id="BaseCompanyMap" type="com.wx.wlcx.model.CMCompany" >
    <id column="COID" property="cmcoid" jdbcType="NCHAR" />
    <result column="NDID" property="ndid" jdbcType="NCHAR" />
    <result column="CPID" property="cpid" jdbcType="NCHAR" />
    <result column="CO_Base" property="coBase" jdbcType="INTEGER" />
    <result column="CO_Type" property="coType" jdbcType="INTEGER" />
    <result column="LG_Code" property="lgCode" jdbcType="NVARCHAR" />
    <result column="CO_Name" property="coName" jdbcType="NVARCHAR" />
    <result column="CO_Addr" property="coAddr" jdbcType="NVARCHAR" />
    <result column="CO_Time" property="coTime" jdbcType="TIMESTAMP" />
    <result column="CO_State" property="coState" jdbcType="INTEGER" />
    <result column="CO_Share" property="coShare" jdbcType="INTEGER" />
    <result column="CO_Phone" property="coPhone" jdbcType="NVARCHAR" />
    <result column="CO_Spell" property="coSpell" jdbcType="NVARCHAR" />
    <result column="CO_Linker" property="coLinker" jdbcType="NVARCHAR" />
    <result column="CO_LkCode" property="coLkcode" jdbcType="NVARCHAR" />
    <result column="CO_E_Mail" property="coEMail" jdbcType="NVARCHAR" />
    <result column="CO_BkType" property="coBktype" jdbcType="INTEGER" />
    <result column="CO_BkCode" property="coBkcode" jdbcType="NVARCHAR" />
    <result column="CO_AcName" property="coAcname" jdbcType="NVARCHAR" />
    <result column="CO_VcType" property="coVctype" jdbcType="NVARCHAR" />
    <result column="CO_VcName" property="coVcname" jdbcType="NVARCHAR" />
    <result column="CO_TxCode" property="coTxcode" jdbcType="NVARCHAR" />
    <result column="CO_BcFate" property="coBcfate" jdbcType="INTEGER" />
    <result column="CO_Stable" property="coStable" jdbcType="INTEGER" />
    <result column="CO_VWRate" property="coVwrate" jdbcType="DOUBLE" />
    <result column="CO_Router" property="coRouter" jdbcType="NVARCHAR" />
    <result column="CO_Server" property="coServer" jdbcType="NVARCHAR" />
    <result column="CO_ReMark" property="coRemark" jdbcType="NVARCHAR" />
    <result column="CO_MapX" property="coMapx" jdbcType="DOUBLE" />
    <result column="CO_MapY" property="coMapy" jdbcType="DOUBLE" />
    <result column="CO_LaType" property="coLatype" jdbcType="INTEGER" />
    <result column="CO_Starts" property="coStarts" jdbcType="INTEGER" />
    <result column="CO_PicLogo" property="coPiclogo" jdbcType="NVARCHAR" />
    <result column="CO_Introduction" property="coIntroduction" jdbcType="NVARCHAR" />
    <result column="CO_Survev" property="coSurvev" jdbcType="NVARCHAR" />
    <collection property="cmMatchers" ofType="CMMatcher" >
      <id column="COID" property="coid" jdbcType="NCHAR" />
      <id column="CR_Idx" property="crIdx" jdbcType="INTEGER" />
      <result column="CR_Mode" property="crMode" jdbcType="INTEGER" />
      <result column="CT_Mode" property="ctMode" jdbcType="INTEGER" />
      <result column="CR_Fate" property="crFate" jdbcType="DOUBLE" />
      <result column="RG_Code" property="rgCode" jdbcType="NVARCHAR" />
      <result column="RG_Name" property="rgName" jdbcType="NVARCHAR" />
      <result column="AR_Code" property="arCode" jdbcType="NVARCHAR" />
      <result column="AR_Name" property="arName" jdbcType="NVARCHAR" />
      <result column="CM_Code" property="cmCode" jdbcType="NVARCHAR" />
      <result column="CM_Name" property="cmName" jdbcType="NVARCHAR" />
      <result column="CR_Addr" property="crAddr" jdbcType="NVARCHAR" />
      <result column="CR_Style" property="crStyle" jdbcType="INTEGER" />
      <result column="CR_Grade" property="crGrade" jdbcType="INTEGER" />
    </collection>
  </resultMap>
  
    <select id="findCompany" resultMap="BaseCompanyMap" >
select cc.*, cm.* from CM_Company cc left join CM_Matcher cm on cc.coid = cm.coid
<where>
  <if test="keyword != null">
    cc.CO_Name like '%${keyword}%'
  </if>

  <if test="arCode != null">
    or cm.AR_Code = #{arCode}
  </if>

  <if test="arName != null">
   and cm.AR_Name like '%${arName}%'
  </if>
</where>
  ORDER BY cm.CR_Style

修改之后的sql及resultMap（将CMMatcher 中的coid 修改为cmcoid）:

<id column="CMCOID" property="coid" jdbcType="NCHAR" />

打印执行的sql:

SELECT TOP 1 * FROM 
(SELECT ROW_NUMBER() OVER (ORDER BY CR_Style) PAGE_ROW_NUMBER, * FROM 
   (SELECT 
 cc.*,  
  cm.COID as CMCOID, cm.CR_Idx, cm.CR_Mode, cm.CT_Mode, cm.CR_Fate, cm.RG_Code, cm.RG_Name,
    cm.AR_Code, cm.AR_Name, cm.CM_Code, cm.CM_Name, CR_Addr, CR_Style, CR_Grade
    FROM [wx].[dbo].[CM_Company] cc LEFT JOIN [wx].[dbo].[CM_Matcher] cm ON cc.coid = cm.coid WHERE cc.CO_Name LIKE '%武汉%') 
   AS PAGE_TABLE_ALIAS )
 AS PAGE_TABLE_ALIAS WHERE PAGE_ROW_NUMBER > 1 ORDER BY PAGE_ROW_NUMBER;