id, activityId, pssId, address, step, report, startDate, endDate,updateTime SELECT * FROM sp_activity_survey WHERE activityId is null SELECT * FROM sp_activity_survey WHERE activityId = #{value} order by step SELECT * FROM sp_activity_survey WHERE 1 = 1 and pssId = #{param1} and address = #{param2} and step = #{param3} SELECT *, (IF(step1 is NULL, '未勘察', '已勘察')) AS status1, (IF(step2 is NULL, '未勘察', '已勘察')) AS status2, (IF(step3 is NULL, '未勘察', '已勘察')) AS status3 FROM ( SELECT activityId, max(comName) as comName,max(address) as address, max(updateTime) as updateTime, sum(CASE WHEN step = '1' THEN step END) AS step1, max(CASE WHEN step = '1' THEN surveyPerson END) AS person1, sum(CASE WHEN step = '2' THEN step END) AS step2, max(CASE WHEN step = '2' THEN surveyPerson END) AS person2, sum(CASE WHEN step = '3' THEN step END) AS step3, max(CASE WHEN step = '3' THEN surveyPerson END) AS person3 FROM `sp_activity_survey` and updateTime>=concat(#{updateTimeStart},' 00:00:00') GROUP BY activityId ) t AND ( comName like CONCAT('%', TRIM(#{keywords}), '%') OR address like CONCAT('%', TRIM(#{keywords}), '%') ) and activityId=(select id from sp_activity s where s.activityNo=#{activityNo}) SELECT *, (IF(step1 is NULL, '未勘察', '已勘察')) AS status1, (IF(step2 is NULL, '未勘察', '已勘察')) AS status2, (IF(step3 is NULL, '未勘察', '已勘察')) AS status3 FROM ( SELECT activityId, max(comName) as comName,max(address) as address, max(updateTime) as updateTime, sum(CASE WHEN step = '1' THEN step END) AS step1, max(CASE WHEN step = '1' THEN surveyPerson END) AS person1, max(CASE WHEN step = '1' THEN report END) AS report1, max(CASE WHEN step = '1' THEN concat(DATE_FORMAT(startDate,'%Y-%m-%d %H:%i'),' ~ ',DATE_FORMAT(endDate,'%Y-%m-%d %H:%i')) END) AS surveyDate1, sum(CASE WHEN step = '2' THEN step END) AS step2, max(CASE WHEN step = '2' THEN surveyPerson END) AS person2, max(CASE WHEN step = '2' THEN report END) AS report2, max(CASE WHEN step = '2' THEN concat(DATE_FORMAT(startDate,'%Y-%m-%d %H:%i'),' ~ ',DATE_FORMAT(endDate,'%Y-%m-%d %H:%i')) END) AS surveyDate2, sum(CASE WHEN step = '3' THEN step END) AS step3, max(CASE WHEN step = '3' THEN surveyPerson END) AS person3, max(CASE WHEN step = '3' THEN report END) AS report3, max(CASE WHEN step = '3' THEN concat(DATE_FORMAT(startDate,'%Y-%m-%d %H:%i'),' ~ ',DATE_FORMAT(endDate,'%Y-%m-%d %H:%i')) END) AS surveyDate3 FROM `sp_activity_survey` activityId=#{id} ) t