LEFT JOIN 右表数据多条导致查询重复解决办法

相信大家都遇到过。A表有2条数据，B表有10条数据，A,B LEFT JOIN 查出来的数据不是2条。而是多条，遇到这种情况怎么办的。可以在代码中控制一下。

resultType
resultType可以把查询结果封装到pojo类型中，但必须pojo类的属性名和查询到的数据库表的字段名一致。 
如果sql查询到的字段与pojo的属性名不一致，则需要使用resultMap将字段名和属性名对应起来，进行手动配置封装，将结果映射到pojo中
！

resultMap

resultMap可以实现将查询结果映射为复杂类型的pojo，比如在查询结果映射对象中包括pojo和list实现一对一查询和一对多查询。

数据库字段：user_id,

实体类字段：userId

需要手动配置设置resultMap

Mapper中基本查询语句

<!-- 查询所有的订单数据 -->
    <!-- resultMap:填入配置的resultMap标签的id值 -->
    <select id="queryOrderAll" resultMap="orderResultMap">
        SELECT id, user_id,
        number,
        createtime, note FROM `order`
    </select>
resultMap中字段映射

<!-- resultMap最终还是要将结果映射到pojo上，type就是指定映射到哪一个pojo -->
    <!-- id：设置ResultMap的id -->
    <resultMap type="order" id="orderResultMap">
        <!-- 定义主键 ,非常重要。如果是多个字段,则定义多个id -->
        <!-- property：主键在pojo中的属性名 -->
        <!-- column：主键在数据库中的列名 -->
        <id property="id" column="id" />
 
        <!-- 定义普通属性 -->
        <result property="userId" column="user_id" />
        <result property="number" column="number" />
        <result property="createtime" column="createtime" />
        <result property="note" column="note" />
    </resultMap>

例子2

mapper.xml中

    <resultMap id="BaseResultMap" type="com.hisense.sys.entity.MyClothes">
        <id column="id" property="id" />
        <result column="user_id" property="userId" />
        <result column="clothes_id" property="clothesId" />
        <result column="clothes_name" property="clothesName" />
        <result column="big_class_label" property="bigClassLabel" />
        <result column="big_class_name" property="bigClassName" />
        <result column="small_class_id" property="categoryId" />
        <result column="small_class_label" property="categoryName" />
        <result column="small_class_name" property="smallClassName" />
        <result column="clothes_pic_url" property="thumbnail" />
        <result column="clothes_gender" property="clothesGender" />
        <result column="rfid" property="rfid" />
        <result column="rfid_last_four" property="rfidLastFour" />
        <result column="clothes_type" property="clothesType" />
        <result column="can_try" property="canTry" />
        <result column="can_buy" property="canBuy" />
        <result column="minipro_url" property="miniproUrl" />
        <result column="status" property="status" />
        <result column="create_date" property="createDate" />
        <result column="color_id" property="colorId" />
        <result column="virtual_flag" property="virtualFlag" />
        <result column="season" property="season" />
        <collection property="myClothesDetailList"  ofType="com.hisense.sys.entity.MyClothesDetail">
            <result property="clothesPropertyCode" column="clothesPropertyCode" />
            <result property="clothesPropertyType" column="clothesPropertyType" />
            <result property="clothesPropertyName" column="clothesPropertyName" />
        </collection>
    </resultMap>

<select id="getMyClothesList" parameterType="map" resultMap="BaseResultMap">
        SELECT
    t.user_id,t.season,
            t.id,
            t.clothes_name,
            t.clothes_id,
            t.color_id,
            t.big_class_label,
            t.big_class_name,
            t.small_class_label,
            t.small_class_name,
            t.create_date,
            t.clothes_pic_url,
            t.small_class_id,
            t.clothes_type,
            t.clothes_gender,
            t.can_buy,
            t.can_try,
            t.minipro_url,
            t.rfid,
            t.rfid_last_four,
            t1.clothes_property_code  clothesPropertyCode,
            t1.clothes_property_name  clothesPropertyName,
            t1.clothes_property_type clothesPropertyType
        FROM `t_my_clothes` t LEFT JOIN t_my_clothes_detail t1 ON t.id = t1.my_clothes_id
        where t.id in( SELECT temp.id from (select t2.id from t_my_clothes t2 where   t2.status = '0' and t2.clothes_gender = #{sex}
		
		<if test="sex!=null and sex !=0">
            and ( t2.user_id =  #{userId} or  t2.user_id ='da89a033-7261-4351-84cb-56375649d3f3')
        </if>
        <if test="sex!=null and sex ==0">
            and ( t2.user_id =  #{userId} or  t2.user_id ='488be7e1-a890-46a5-9a8f-807fd29c60a1')
        </if>
		
        <if test="bigClassLabel!=null and bigClassLabel !=''">
            and t2.big_class_label = #{bigClassLabel}
        </if>
        <if test="smallClassLabel!=null and smallClassLabel !=''">
            and t2.small_class_label =#{smallClassLabel}
        </if>
        <if test="season!=null and season !=''">
            and t2.season =#{season}
        </if>
        <if test="pageNum!=null and pageNum !=''">
            limit #{pageNum},#{pageSize}
        </if>
        ) as temp )
        <if test="checkFlag!=null and checkFlag !=''">
            and t.virtual_flag = #{checkFlag}
        </if>
        order by t.create_date desc
    </select>