case when用法sql

一、简单CASE WHEN函数：

CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END
 
# 使用 IF 函数进行替换
IF(SCORE = 'A', '优', '不及格')

友情提示：

 THEN后边的值与ELSE后边的值类型应一致，否则会报错。如下：
CASE SCORE WHEN ‘A’ THEN ‘优’ ELSE 0 END’优’和0数据类型不一致则报错：
[Err] ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER

二、CASE WHEN条件表达式函数

格式：
CASE WHEN condition THEN result
 
[WHEN...THEN...]
 
ELSE result
 
END


转化sql语法：

CASE 
	 WHEN SCORE = 'A' THEN '优'
     WHEN SCORE = 'B' THEN '良'
     WHEN SCORE = 'C' THEN '中' 
     ELSE '不及格' END
 
# 等同于
CASE score
    WHEN 'A' THEN '优'
    WHEN 'B' THEN '良'
    WHEN 'C' THEN '中'
    ELSE '不及格' END

下面来看简单的业务场景应用：比如说想在某表中汇总各年龄段的人，表中是按会员年龄字段分别存储的明细数据。

SELECT
    CASE
        WHEN age BETWEEN 18 AND 23 THEN '18-23岁'
        WHEN age BETWEEN 24 AND 30 THEN '24-30岁'
        WHEN age BETWEEN 31 AND 40 THEN '31-40岁'
        WHEN age BETWEEN 41 AND 50 THEN '41-50岁'
        WHEN age BETWEEN 51 AND 60 THEN '51-60岁'
        WHEN age >=61 THEN '61岁以上'
        ELSE '其他'
    END AS age_group,
    COUNT(*) AS count
FROM
    表名
where 
		`data_date` BETWEEN	'20241010' 
	AND '20241016' 
	AND `mem_type` = '1'
GROUP BY
    age_group;