原SQL:
<select id="getMyMissionList" resultType="java.util.HashMap"> SELECT rm.id missionId, rpu.id paperUserId, rm.cover, if(rm.class_id is NULL,1,2) bookStatus, rm.`name`, DATE_FORMAT(rm.end_time,'%Y-%m-%d %H:%i:%s') endTime, rm.topic_id topicId, rm.create_user_id createUserId, IF(rue.id is NULL,1,2) missionStatus, rr.`level` FROM re_mission rm JOIN re_res rr on rr.fanya_res_id=rm.topic_id left JOIN re_user_evaluate rue ON rue.uid=rm.create_user_id and rue.topic_id=rm.topic_id left JOIN re_paper_user rpu on rpu.uid = #{param.uid} and rpu.topic_id=rm.topic_id and rpu.fid=rm.fid and rpu.is_max_lv=1 WHERE ((rm.class_id = #{param.classId} AND rm.mission_type=2) OR (rm.create_user_id = #{param.uid} AND rm.mission_type=1)) and rm.publish_status in (1,3) AND rm.fid = #{param.fid} GROUP BY rm.id ORDER BY rm.id DESC LIMIT #{param.startNumber},#{param.pageSize} </select>
改造后SQL:
<select id="getMyMissionList" resultType="java.util.HashMap"> SELECT rm.id missionId, MAX(if(rpu.id is NULL,0,rpu.id)) paperUserId, rm.cover, if(rm.class_id is NULL,1,2) bookStatus, rm.`name`, DATE_FORMAT(rm.end_time,'%Y-%m-%d %H:%i:%s') endTime, rm.topic_id topicId, rm.create_user_id createUserId, max(IF(rue.id is NULL,1,2)) missionStatus, rr.`level` FROM re_mission rm JOIN re_res rr on rr.fanya_res_id=rm.topic_id left JOIN re_user_evaluate rue ON rue.uid= #{param.uid} and rue.topic_id=rm.topic_id left JOIN re_paper_user rpu on rpu.uid = #{param.uid} and rpu.topic_id=rm.topic_id and rpu.fid=rm.fid and rpu.is_max_lv=1 WHERE ((rm.class_id = #{param.classId} AND rm.mission_type=2) OR (rm.create_user_id = #{param.uid} AND rm.mission_type=1)) and rm.publish_status in (1,3) AND rm.fid = #{param.fid} GROUP BY rm.id ORDER BY rm.id DESC LIMIT #{param.startNumber},#{param.pageSize} </select>
用了left join后要使用聚合函数取值,否则会被覆盖。