mysql case when then else end 语句

case when then else end 语法应用场景还是比较多的

1、排序：select stuno,name,math,case math when 90 then ‘优秀’ when 80 then ‘良好’ end as sort from class order by sort desc
2、根据不同字段的值关联不同的表
select left(operation_time,16) as
operation_time,a.operator_id as pc_user_id,a.operator_type,a.operation_content,(case when a.operator_type = 1 then b.pu_name else c.su_name end) as pu_name from object_audit_log as a
left join pc_user as b on
operator_id=pc_user_id
left join system_user as c on
operator_id=system_user_id；
3、配合聚合函数做数据统计
select count(case when att_system_status=0 and att_status=3 and att_fixed=0 and att_rented_status =1 then 1 end ) as count_publishing,count(case when att_fixed then 1 end ) as count_fixed,count(case when att_system_status=0 and att_status=2 and att_fixed=0 and att_rented_status =1 then 1 end ) as count_auditing,count(case when att_system_status=1 and att_status=3 and att_fixed=0 and att_rented_status =1 then 1 end ) as count_canclepublish,count(case when att_status=0 and att_fixed=0 and att_rented_status =1 then 1 end ) as count_nosubmit from property a left join apartment on a.property_id = att_property_id left join property_company on a.ppt_property_company_id = property_company_id left join city on a.ppt_city_id = city_id;