1 <select id="list" parameterType="com.gcsoft.pyas.bizModule.myTraining.dto.MyTrainingApplyDto" resultMap="BaseResultMap"> 2 SELECT * FROM ( 3 SELECT 4 <![CDATA[ 5 CASE WHEN to_char(sysdate,'yyyy-mm-ddHH24:mi') >= (to_char(mytrain.TRAIN_START_DATE,'yyyy-mm-dd') || mytrain.TRAIN_START_TIME) AND 6 to_char(sysdate,'yyyy-mm-ddHH24:mi') < (to_char(mytrain.TRAIN_END_DATE,'yyyy-mm-dd') || mytrain.TRAIN_END_TIME) 7 THEN '4-2' 8 WHEN to_char(sysdate,'yyyy-mm-ddHH24:mi') < (to_char(mytrain.TRAIN_START_DATE,'yyyy-mm-dd') || mytrain.TRAIN_START_TIME) 9 THEN '4-1' 10 WHEN to_char(sysdate,'yyyy-mm-ddHH24:mi') >= (to_char(mytrain.TRAIN_END_DATE,'yyyy-mm-dd') || mytrain.TRAIN_END_TIME) 11 THEN '4-3' 12 END AS TRAIN_STATUS, 13 ]]> 14 CASE WHEN tli.LEAVE_STATUS IS NULL THEN '-' ELSE tli.LEAVE_STATUS END AS LEAVE_STATUS, 15 CASE WHEN ttoi.APPROVE_STATUS IS NULL THEN '未开始' ELSE ttoi.APPROVE_STATUS END AS OUTPUT_STATUS, 16 --CASE WHEN ttoi.APPROVE_STATUS IS NULL THEN '-' ELSE '已评价' END AS EVALUATE_STATUS, 17 <include refid="Base_Column_List"/> 18 FROM ( 19 <![CDATA[ 20 SELECT tti.* FROM (SELECT * FROM TB_TRAINING_INFO WHERE TRAIN_STATUS = '4' AND DELETE_FLAG = '0') tti 21 LEFT JOIN (SELECT BUS_ID, PARTICIPANT FROM TB_BUS_PARTICIPANTS_INFO WHERE BUS_TYPE = '0' and PARTICIPANT = #{currentUserName,jdbcType=CHAR} AND DELETE_FLAG = '0') tbpi on tbpi.BUS_ID = tti.ID 22 LEFT JOIN (SELECT TRAIN_ID, ENROLLEE FROM TB_TRAINING_ENROLL_INFO WHERE ENROLLEE = #{currentUserName,jdbcType=CHAR} AND ENROLL_STATUS = '3' AND DELETE_FLAG = '0') tei on tei.TRAIN_ID = tti.ID 23 WHERE (tti.TRAIN_PARTICIPANT_TYPE = '1' AND tti.NEED_ENROLL = '0') 24 OR (tti.TRAIN_PARTICIPANT_TYPE = '1' AND tti.NEED_ENROLL <> '0' AND tei.ENROLLEE IS NOT NULL) 25 OR (tti.TRAIN_PARTICIPANT_TYPE <> '1' AND tti.NEED_ENROLL = '0' AND tbpi.PARTICIPANT IS NOT NULL) 26 OR (tti.TRAIN_PARTICIPANT_TYPE <> '1' AND tti.NEED_ENROLL <> '0' AND tei.ENROLLEE IS NOT NULL) 27 ]]> 28 ) mytrain 29 LEFT JOIN (SELECT TRAIN_ID, LEAVE_STATUS FROM TB_TRAINING_LEAVE_INFO WHERE LEAVER = #{currentUserName,jdbcType=CHAR}) tli on tli.TRAIN_ID = mytrain.ID 30 LEFT JOIN (SELECT TRAIN_ID, APPROVE_STATUS FROM TB_TRAINING_SUMMARY WHERE SUBMITTER = #{currentUserName,jdbcType=CHAR}) ttoi on ttoi.TRAIN_ID = mytrain.ID 31 ) 32 <where> 33 <if test="trainName != null and trainName != ''"> 34 and TRAIN_NAME LIKE concat(concat('%',#{trainName,jdbcType=OTHER}),'%') 35 </if> 36 <if test="trainStatus != null and trainStatus != ''"> 37 and TRAIN_STATUS = #{trainStatus, jdbcType=CHAR} 38 </if> 39 <if test="needEnroll != null and needEnroll != ''"> 40 and NEED_ENROLL = #{needEnroll, jdbcType=CHAR} 41 </if> 42 </where> 43 order by case when TRAIN_STATUS = '4-2' then 1 when TRAIN_STATUS = '4-1' then 2 else 3 end, TRAIN_START_DATE 44 </select>