case end 的用法

 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>

 

转载于:https://www.cnblogs.com/zhengyuanyuan/p/10936183.html

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值