MySQL学习-- case表达式

-- case 表达式语法
CASE SEX
WHEN 'F' THEN 'Female'
ELSE 'Male'
END AS SEX; //这里的as sex是以sex为列名，如不列的话，列名则为case sex
-- 等同于如下IF-THEN-ELSE
IF SEX = 'F' THEN
RETURN 'Female'
ELSE
RETURN 'Male'
ENDIF;

-- 示例：获取1980年后加入俱乐部的每个球员的号码、性别和名字。性别必须显示为'Female'或'Male'
SELECT PLAYERNO, 
CASE SEX
WHEN 'F' THEN 'Female'
WHEN 'M' THEN 'Male' 
END AS SEX,
NAME
FROM PLAYERS
WHERE JOINED > 1980;

-- 多个when条件可以包含到一个CASE表达式中
CASE TOWN
WHEN 'Straford' THEN 0
WHEN 'Plymouth' THEN 1
WHEN 'Inglewood' THEN 2
ELSE 3
END ;

--示例：假设网球俱乐部把所有罚款划分为3类。第一类low,包括所有大于0小于等于40的罚款，第二类moderate包含所有在41到80之间的罚款，第三类high包含所有大于80的罚款。接下来，找出每次罚款的支付编号，数量以及相应的分类

SELECT PAYMENTNO,
AMOUNT,
CASE 
WHEN AMOUNT >=0 and AMOUNT<=40 THEN 'low'
WHEN AMOUNT >=41 and AMOUNT <=80 THEN 'moderate'
WHEN AMOUNT >= 81 THEN 'hign'
ELSE 'incorrent'
END AS TYPE
from PENALTIES;

-- 示例：找出属于low一类的罚款的编号
SELECT PAYMENTNO,AMOUNT
FROM PENALTIES
where CASE 
WHEN AMOUNT>=0 and AMOUNT <=40 THEN 'low'
WHEN AMOUNT >=41 and AMOUNT <= 80 then 'moderate'
when AMOUNT >80 then 'hign'
ELSE 'incorrent'
end = 'low';