sql根据时间生成状态排序

根据时间生成状态排序
SELECT a.*,(
CASE
WHEN b.end_time <![CDATA[>=]]> now() AND start_time <![CDATA[<]]> now() THEN 1
WHEN b.start_time <![CDATA[>]]> now() THEN 2
WHEN b.end_time <![CDATA[<=]]> now() THEN 3
END
) AS couponStatus FROM user_coupons a left join free_coupon b on a.coupons_id = b.id left join user_info d on a.user_id = d.id
<where>
    <include refid="pageSearchSql"/>
</where>
GROUP BY a.id order by couponStatus asc ,a.create_time desc
limit #{offset},#{pagesize}