mysql case...when的用法

每个Case表达式都是以一个when定义开始的。

形式一：

获取1980年后加入俱乐部的每个球员的号码、性别、名字。性别必须显示问 ‘Female’或者‘Male’。

select playno,

　　　case sex when 'F' then 'Female'

　　　else 'Male' end as sex,

　　　name

from players

where joined > 1980

 

形式二：

如果else省略了，并且sex列的值不等于when所定义的标量表达式中的一个的话，将会返回空值。

select playno,

　　　case sex

　　  when 'F' then 'Famale' end as females，

　　　name

from players

where joined > 1980

实例：

SELECT um.uid as '用户ID',um.username as '用户名',um.email as '邮箱',usf.mobile as '电话', uq.answer as '医院',usf.workunit as '工作单位',usf.jobtitle as '职称',usf.kee as '一级科室',usf.keee as '二级科室',usf.keeee as '三级科室',

CASE uug.gid

WHEN 1 THEN '移动卡'

WHEN 2 THEN '联通卡'

WHEN 4 THEN '京东卡'

WHEN 5 THEN '当当卡'

END AS "礼品"

FROM uchome_spacefield usf left JOIN uchome_member um ON usf.uid = um.uid

LEFT JOIN uchome_usergifts uug ON uug.uid = usf.uid

LEFT JOIN uchome_ques uq ON uq.uid = uug.uid

WHERE uug.replystatus = 2  AND uq.tcode='E0B67776E5A14a6e821C3E2155402890';