SQL的case-when使用示例

ActivityBaseInfoMapper.sql

      SELECT
            a.activity_id,
            a.id AS activity_round_id,
            a.round_num,
            a.round_start_time,
            a.round_end_time,
            a.reward_time,
            b.start_time AS activity_start_time,
            b.end_time AS activity_end_time,
            b.draw_reward_type,
            b.max_lottery_code,
            b.bean_transfer_num,
            c.activity_type_key,
            d.activity_template_key,
            -- >是在xml中的转义
            IF ( a.reward_time - CURRENT_TIMESTAMP () > 0, (a.reward_time - CURRENT_TIMESTAMP ()) DIV 1000, 0 ) AS
            reward_countdown_time,
            CASE WHEN (a.draw_status = 2) THEN 1 WHEN (CURRENT_TIMESTAMP () < a.reward_time) THEN 0 ELSE 2 END AS
            activity_reward_status,
            e.global_num AS quota_num,
            f.thumb AS activity_image,
            f.`name` AS activity_title,
            f.price AS goods_origin_price,
            f.prize_display_price AS prize_display_price
        FROM
            jmf_activity_round a
        LEFT JOIN jmf_activity_base_info b ON a.activity_id = b.id
        LEFT JOIN jmf_activity_type c ON b.activity_type_id = c.id
        LEFT JOIN jmf_activity_template d ON b.activity_template_id = d.id
        LEFT JOIN jmf_activity_reward_level e ON e.activity_round_id = a.id
        LEFT JOIN jmf_activity_reward_prize f ON f.activity_round_id = a.id