case when then end

select tmp.kaquanbiaoti '卡券标题',tmp.userPhone '注册手机号' ,baseaccount.realName '姓名' ,tmp.jine '金额',

CASE tmp.lingyongzhuangtai
when 'notUsed' then '未使用'
when 'expired' then '已过期'
when 'used' then '已过使用'
END as '领用状态'

,tmp.getTime '领用时间' from
t_money_investor_baseaccount baseaccount
LEFT JOIN
(
select user_coupon.`name` kaquanbiaoti ,user_coupon.phone userPhone ,coupon.couponAmount jine,user_coupon.`status` lingyongzhuangtai,coupon.createTime getTime from
t_tulip_user_coupon user_coupon
right join
t_tulip_coupon coupon
on
user_coupon.couponBatch = coupon.oid
)tmp
on
baseaccount.phoneNum = tmp.userPhone
where tmp.getTime BETWEEN '2017-10-31 23:59:59' and '2017-11-30 23:59:59'