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