CASE WHEN THEN END用法

user表中sex字段，1代表男，2代表女，查询结果显示性别字段

1、select *,case sex when 1 then '男' when 2 then '女' else '其他' end as '性别' from user
2、select *,case when sex = 1 then '男' when sex = 2 then '女' else '其他' end as '性别' from user

order表，查询是首次下单和非首次下单金额

select round(sum(case when o.order_count > 1 then 0 else o.amount end),2) as first,
	   round(sum(case when o.order_count > 1 then o.amount else 0 end),2) as many 
	   from order o

上面的sql就是：

--非首次下单
select round(sum(t.amount),2) from ( SELECT amount from order where order_count > 1) as t
--首次下单
select round(sum(t.amount),2) from (select amount from order where order_countt = 1) as t