CASE WHEN THEN 的简单用法;
有如下表格;
判断分数等级
SELECT *,
CASE WHEN DEGREE > 90 THEN '优秀'
WHEN DEGREE BETWEEN 70 AND 90 THEN '良好'
WHEN DEGREE BETWEEN 60 AND 70 THEN '一般'
ELSE '不及格'
END AS [等级]
FROM STUDENT AS S JOIN SCORE
ON S.SNO = SCORE.SNO;
Case具有两种格式。简单Case函数和Case搜索函数。
简单Case函数
CASE sex
WHEN ‘1’ THEN ‘男’
WHEN ‘2’ THEN ‘女’
ELSE ‘其他’ END
–Case搜索函数
CASE WHEN sex = ‘1’ THEN ‘男’
WHEN sex = ‘2’ THEN ‘女’
ELSE ‘其他’ END
Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。
–比如说,下面这段SQL,你永远无法得到“第二类”这个结果
CASE WHEN col_1 IN ( ‘a’, ‘b’) THEN ‘第一类’
WHEN col_1 IN (‘a’) THEN ‘第二类’
ELSE’其他’ END