SQL之CASE WHEN用法详解

一、CASE WHEN基础用法详解

语法格式:

CASE WHEN condition THEN result

[WHEN...THEN...] ELSE result

END

SELECT
STUDENT_NAME,
(CASE WHEN score < 60 THEN '不及格'
WHEN score >= 60 AND score < 80 THEN '及格'
WHEN score >= 80 THEN '优秀'
ELSE '异常' END) AS REMARK
FROM
TABLE

二、where语句后跟case语句

select *
from A, B
where
CASE
when A.id = 0 then B.name in ('aaa', 'bbb')
when A.id = 1 then B.name in ('bbb', 'ccc')
when A.id = 2 then B.name in ('ccc', 'ddd')
END

三、Update语句使用case更新

update t_salary
 set 
 salary = 
 (
  case 
   when salary < 3000 then salary + salary * 0.2
   when salary >= 3000 then salary + salary * 0.08
   else salary 
  end
 )