应用场景一:当更新Test表的时候,只对特定的字段更新,可以采取下面的方法。下面的方法只对字段 BeginTime 为 NULL 的 BeginTime 进行更新,BeginTime 不为 NULL 的情况不更新,而且其余字段 EndTime 也可以同时更新。
UPDATE Test
SET BeginTime = CASE
WHEN BeginTime IS NULL THEN
"2014-12-21 12:15:13"
ELSE
BeginTime
END,
EndTime = "2014-12-21 12:15:13"
应用场景二:SQL查询时嵌入CASE WHEN。如判断工资的等级,并统计每一等级的人数,可以用下面的SQL语句。
SELECT
CASE
WHEN salary <= 1500 THEN
'1'
WHEN salary > 1500
AND salary <= 2000 THEN
'2'
WHEN salary > 2000
AND salary <= 3000 THEN
'3'
WHEN salary > 3000
AND salary <= 4000 THEN
'4'
ELSE
NULL
END salary_class,
COUNT(*)
FROM
Table_A
GROUP BY
CASE
WHEN salary <= 1500 THEN
'1'
WHEN salary > 1500
AND salary <= 2000 THEN
'2'
WHEN salary > 2000
AND salary <= 3000 THEN
'3'
WHEN salary > 3000
AND salary <= 4000 THEN
'4'
ELSE
NULL
END;