SELECT CASE WHEN的使用小案例

需求：分页查询广告需要根据广告的上下线时间段判断广告的上线状态，排序规则需要让上线状态排在前面

使用case when函数来查询广告的状态值，并在排序中使用该字段。

<select id="pageList" resultMap="bannerMap"
        parameterType="com..BannerResourcePageDTO">

SELECT
b.id ,b.title ,
CASE
WHEN (now() >= online_time and NOW() <![CDATA[ <= ]]> offline_time) THEN
0
WHEN (now() <![CDATA[ <= ]]> online_time) THEN
1
ELSE
2
END onlinestatus,
CASE
WHEN (now() >= online_time and NOW() <![CDATA[ <= ]]> offline_time) THEN
pb.order_num
ELSE
0
END ordernum
FROM banner b 

<if test="bannerResourcePageDTO.clickCountOrder != null and bannerResourcePageDTO.clickCountOrder == true ">
    ORDER BY onlinestatus ASC ,click_count ASC, ordernum ASC, b.create_time DESC
</if>
<if test="bannerResourcePageDTO.clickCountOrder != null and bannerResourcePageDTO.clickCountOrder == false ">
    ORDER BY onlinestatus ASC ,click_count DESC, ordernum ASC, b.create_time DESC
</if>
<if test="bannerResourcePageDTO.clickCountOrder == null">
    ORDER BY onlinestatus ASC , ordernum ASC, b.create_time DESC
</if>
</select>