1.通过业务编写sql(解决数据重复问题)
从加盟商维度导出联系人及项目(背景,加盟商和联系人及项目之间两两有关系,导出的数据要和加盟商总数相同,但是加盟商对应多个联系人,加盟商又会对应多个项目,所以不应该从加盟商关联联系人,再从联系人关联项目,而应该从加盟商维度出发,用加盟商关联联系人,取其中一个,再用加盟商关联项目,取其中一个(按产品要求))
<select id="selectFranchiseeList" resultType="com.hzgroup.fcrm.model.FranchiseeList" > select t.franchisee_no as franchiseeNo,t.franchisee_name as franchiseeName,t.hotel_total as hotelTotal,t3.phone,t3.mail,t3.user_name as userName,t.type as type,t5.develop_no as developNo,t5.project_name as projectName,t5.brand from ( select t1.franchisee_name,t1.franchisee_no,t1.hotel_total,t1.type,t2.user_id from franchisee_info t1 left join (select user_id as user_id,franchisee_no from contact_franchisee where user_id in (select min(user_id) from contact_franchisee where type_code = 1 group by franchisee_no)) t2 on t1.franchisee_no = t2.franchisee_no) t left join contact_info t3 on t.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 t.franchisee_no = t6.franchisee_no left join hotel_basic_info t5 on t6.hotel_unify_no = t5.hotel_unify_no <where> <if test="franchiseeName!=null and franchiseeName!=''"> and t.franchisee_name = #{franchiseeName} </if> <if test="franchiseeNo!=null and franchiseeNo!=''"> and t.franchisee_no = #{franchiseeNo} </if> <if test="type!=null and type!=''"> and t.type = #{type} </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,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 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 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> <if test="hotelUnifyNo!=null and hotelUnifyNo!=''"> and t.hotel_unify_no = #{hotelUnifyNo} </if> <if test="hotelName!=null and hotelName!=''"> and t.hotel_name = #{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="brandIdList!=null and brandIdList.size()>0"> and t.brand_id in <foreach collection="brandIdList" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </if> </where> </select>