mybatis 踩坑记录

1.通过业务编写sql（解决数据重复问题）

从加盟商维度导出联系人及项目（背景，加盟商和联系人及项目之间两两有关系，导出的数据要和加盟商总数相同，但是加盟商对应多个联系人，加盟商又会对应多个项目，所以不应该从加盟商关联联系人，再从联系人关联项目，而应该从加盟商维度出发，用加盟商关联联系人，取其中一个，再用加盟商关联项目，取其中一个（按产品要求））

<select id="selectFranchiseeList" resultType="com.hzgroup.fcrm.model.FranchiseeList" >
    select t1.franchisee_no as franchiseeNo,t1.franchisee_name as franchiseeName,t1.hotel_total as hotelTotal,t3.phone,t3.mail,t3.user_name as userName,t1.type as type,t5.develop_no as developNo,t5.project_name as projectName,t5.brand
    from franchisee_info t1
    left join contact_franchisee t2 on t1.franchisee_no = t2.franchisee_no and t2.type_code = 1
    left join contact_info t3 on t2.user_id = t3.user_id
    left join (select hotel_unify_no,franchisee_no from franchisee_hotel where id in (select max(id) from franchisee_hotel group by franchisee_no )) t6 on  t1.franchisee_no = t6.franchisee_no
    left join hotel_basic_info t5 on  t6.hotel_unify_no = t5.hotel_unify_no
    <where>
      t1.deleted = 1
      <if test="franchiseeName!=null and franchiseeName!=''">
        and t1.franchisee_name like '%' #{franchiseeName} '%'
      </if>
      <if test="franchiseeNo!=null and franchiseeNo!=''">
        and t1.franchisee_no like '%' #{franchiseeNo} '%'
      </if>
      <if test="type!=null and type!=''">
        and t1.type = #{type}
      </if>
      <if test="franchiseeNoList!=null and franchiseeNoList.size()>0">
        and t1.franchisee_no in
        <foreach collection="franchiseeNoList" index="index" item="item" open="(" separator="," close=")">
          #{item}
        </foreach>
      </if>
    </where>
  </select>

　　

2.mysql： only_full_group_by问题

对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

select a,b from table group by a,b,c; (正确) 
select a,b,c from table group by a,b; (错误) 

如果查询的是函数则不会报这个错误；例：

AVG([distinct] expr)

求平均值

COUNT({*|[distinct] } expr)

统计行的数量

MAX([distinct] expr)

求最大值

MIN([distinct] expr)

求最小值

SUM([distinct] expr)

求累加和

 

3.mybatis如果是list查询条件要判断null和空

<select id="selectHotelInfos" resultType="com.hzgroup.fcrm.model.HotelList">
            select t.develop_no as developNo,t.hotel_unify_no as hotelUnifyNo,t.hotel_name as hotelName,t.hotel_state as hotelState,t.operate_mode as operateMode,t.brand,t.hotel_brand_type as hotelBrandType, t.city,t.province,t.hotel_address as hotelAddress,t.trial_opening_date as trialOpeningDate,t.opening_date as openingDate,t.emp_name as hotelManager,t3.emp_name as customerServiceName
            from
            (select t1.develop_no,t1.hotel_unify_no,t2.hotel_unify_no as hotel_unify_no2,t1.hotel_name,t1.hotel_state,t1.operate_mode_id,t1.operate_mode,t1.brand,t1.hotel_brand_type,t1.city,t1.province,t1.province_no,t1.hotel_address,t1.trial_opening_date,t1.opening_date,t2.emp_name,t1.hotel_state_id,t1.brand_id,t1.hotel_brand_type_id,t1.city_no
            from hotel_basic_info t1
            left join (select job_code,emp_name,hotel_unify_no from hr_hotel_management_position where job_code = "DZ" and is_valid = 1 and is_delete_ext = 0 and  trim(emp_name)!='' group by hotel_unify_no) t2 on t2.hotel_unify_no = t1.hotel_unify_no) t
            left join (select job_code,emp_name,hotel_unify_no from hr_hotel_manager_other where job_code = "JMKF" and is_valid = 1 and is_delete_ext = 0 and  trim(emp_name)!='' group by hotel_unify_no) t3 on t.hotel_unify_no = t3.hotel_unify_no and t.hotel_unify_no2 = t3.hotel_unify_no
            <where>
              t.hotel_state_id != 6 and t.operate_mode_id != 1
              <if test="hotelUnifyNo!=null and hotelUnifyNo!=''">
                  and t.hotel_unify_no like '%' #{hotelUnifyNo} '%'
              </if>
              <if test="hotelName!=null and hotelName!=''">
                  and t.hotel_name like '%' #{hotelName} '%'
              </if>
              <if test="hotelStateId!=null and hotelStateId!=''">
                  and t.hotel_state_id = #{hotelStateId}
              </if>
              <if test="hotelBrandTypeId!=null and hotelBrandTypeId!=''">
                and t.hotel_brand_type_id = #{hotelBrandTypeId}
              </if>
              <if test="provinceNo!=null and provinceNo!=''">
                and t.province_no = #{provinceNo}
              </if>
              <if test="cityNo!=null and cityNo!=''">
                and t.city_no = #{cityNo}
              </if>
              <if test="startDate != null and startDate != ''">
                and t.opening_date <![CDATA[>=]]> #{startDate}
              </if>
              <if test="endDate != null and endDate != ''">
                and t.opening_date <![CDATA[<=]]>#{endDate}
              </if>
              <if test="brandIdList!=null and brandIdList.size()>0">
                and t.brand_id in
                <foreach collection="brandIdList" index="index" item="item" open="(" separator="," close=")">
                  #{item}
                </foreach>
              </if>
              <if test="hotelUnifyNoList!=null and hotelUnifyNoList.size()>0">
                and t.hotel_unify_no in
                <foreach collection="hotelUnifyNoList" index="index" item="item" open="(" separator="," close=")">
                  #{item}
                </foreach>
              </if>
            </where>
  </select>