SQL中的 CASE WHEN用法详解

sql中的 CASE WHEN用法详解

1. 基本用法

SELECT
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        ELSE default_result
    END AS alias_name
FROM
    table_name;

• CASE 开始一个 CASE WHEN 语句块，END 结束该块。
• 按顺序检查每个条件，并在找到第一个为真的条件时返回相应的结果。
• 如果没有条件为真，则返回 ELSE 部分的默认结果（可选）。
• 允许在 WHEN 子句中使用表达式作为条件，而不仅仅是列或单个值。

2. 举例

根据学生的成绩, 分为 A B C 和 D 四个等级, A: 大于等于90; B: [80,90); C: [60,80); D:小于60;

-- 数据准备
WITH t_score_info AS ( -- 员工信息表
SELECT * FROM (
  VALUES    (1001, 'lyf', 83),
            (1002, 'zyb', 72),
            (1003, 'whl', 45),
            (1004, 'lxm', 64),
            (1005, 'szy', 92),
            (1006, 'xjp', 31),
            (1007, 'ply', 42),
            (1008, 'wyb', 88),
            (1009, 'spx', 72),
            (1010, 'yjj', 88),
            (1011, 'teg', 98),
            (1012, 'hxj', 61)
) AS table_name(user_id, name, score)
)
-- CASE WHEN 使用
SELECT user_id, name, score
    , CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 AND score < 90 THEN 'B'
        WHEN score >= 60 AND score < 80 THEN 'C'
        ELSE 'D'
      END AS grades
FROM t_score_info
;

3. 使用 CASE WHEN 语句进行数据分组

-- 数据准备
WITH t_score_info AS ( -- 员工信息表
SELECT * FROM (
  VALUES    (1001, 'lyf', 83, 26),
            (1002, 'zyb', 72, 37),
            (1003, 'whl', 45, 25),
            (1004, 'lxm', 64, 46),
            (1005, 'szy', 92, 36),
            (1006, 'xjp', 31, 65),
            (1007, 'ply', 42, 67),
            (1008, 'wyb', 88, 25),
            (1009, 'spx', 72, 46),
            (1010, 'yjj', 88, 16),
            (1011, 'teg', 98, 18),
            (1012, 'hxj', 61, 48)
) AS table_name(user_id, name, score, age)
)

-- 使用 CASE WHEN 语句进行数据分组
SELECT  
    CASE 
      WHEN age >= 60 THEN '老年'
      WHEN age >= 40 AND age < 60 THEN '中年'
      WHEN age >= 20 AND age < 40 THEN '青年'
      ELSE '少年'
    END AS age_group
    , COUNT(1) AS ct
FROM t_score_info
GROUP BY age_group
;

4. 使用 CASE WHEN 语句修改字段值

-- 数据准备
WITH t_score_info AS ( -- 员工信息表
SELECT * FROM (
  VALUES    (1001, 'lyf', 83, 26, 'C'),
            (1002, 'zyb', 72, 37, 'B'),
            (1003, 'whl', 45, 25, 'C'),
            (1004, 'lxm', 64, 46, 'C'),
            (1005, 'szy', 92, 36, 'B'),
            (1006, 'xjp', 31, 65, 'C'),
            (1007, 'ply', 42, 67, 'B'),
            (1008, 'wyb', 88, 25, 'C'),
            (1009, 'spx', 72, 46, 'B'),
            (1010, 'yjj', 88, 16, 'B'),
            (1011, 'teg', 98, 18, 'A'),
            (1012, 'hxj', 61, 48, 'B')
) AS table_name(user_id, name, score, age, grades)
)

-- 使用 CASE WHEN 语句修改字段值: 将分数分分数段展示, 不展示具体分数值
SELECT user_id, name
    , CASE 
        WHEN score >= 90 THEN '90+'
        WHEN score >= 80 AND score < 90 THEN '80-90'
        WHEN score >= 60 AND score < 80 THEN '60-80'
        ELSE '0-60'
      END AS score
FROM t_score_info
;

5. CASE WHEN 和 聚合函数 一起使用

-- 数据准备
WITH t_score_info AS ( -- 员工信息表
SELECT * FROM (
  VALUES    (1001, 'lyf', 83, 26, 'C'),
            (1002, 'zyb', 72, 37, 'B'),
            (1003, 'whl', 45, 25, 'C'),
            (1004, 'lxm', 64, 46, 'C'),
            (1005, 'szy', 92, 36, 'B'),
            (1006, 'xjp', 31, 65, 'C'),
            (1007, 'ply', 42, 67, 'B'),
            (1008, 'wyb', 88, 25, 'C'),
            (1009, 'spx', 72, 46, 'B'),
            (1010, 'yjj', 88, 16, 'B'),
            (1011, 'teg', 98, 18, 'A'),
            (1012, 'hxj', 61, 48, 'B')
) AS table_name(user_id, name, score, age, grades)
)

-- CASE WHEN 和 聚合函数 一起使用: 计算各个年龄段的总分数
SELECT  
      SUM(CASE WHEN age >= 60 THEN score ELSE 0 END) AS `老年组总分` 
    , SUM(CASE WHEN age >= 40 AND age < 60 THEN score ELSE 0 END) AS `中年组总分` 
    , SUM(CASE WHEN age >= 20 AND age < 40 THEN score ELSE 0 END) AS `青年组总分` 
    , SUM(CASE WHEN age < 20 THEN score ELSE 0 END) AS `少年组总分` 
FROM t_score_info
;

6. CASE WHEN 的嵌套使用

-- 数据准备
WITH t_score_info AS ( -- 员工信息表
SELECT * FROM (
  VALUES    (1001, 'lyf', 83, 26, 'C'),
            (1002, 'zyb', 72, 37, 'B'),
            (1003, 'whl', 45, 25, 'C'),
            (1004, 'lxm', 64, 46, 'C'),
            (1005, 'szy', 92, 36, 'B'),
            (1006, 'xjp', 31, 65, 'C'),
            (1007, 'ply', 42, 67, 'B'),
            (1008, 'wyb', 88, 25, 'C'),
            (1009, 'spx', 72, 46, 'B'),
            (1010, 'yjj', 88, 16, 'B'),
            (1011, 'teg', 98, 18, 'A'),
            (1012, 'hxj', 61, 48, 'B')
) AS table_name(user_id, name, score, age, grades)
)

-- CASE WHEN 的嵌套使用: 不同年龄段,成绩乘上不同系数之后,再判定等级(青年:1.0, 少年:1.0, 中年:1.1, 老年:1.2)
SELECT  user_id, name, score, age,
    CASE 
      WHEN age >= 60 THEN 
        CASE 
        WHEN score*1.5 >= 90 THEN 'A'
        WHEN score*1.5 >= 80 AND score*1.5 < 90 THEN 'B'
        WHEN score*1.5 >= 60 AND score*1.5 < 80 THEN 'C'
        ELSE 'D' END 
      WHEN age >= 40 AND age < 60 THEN 
        CASE 
        WHEN score*1.2 >= 90 THEN 'A'
        WHEN score*1.2 >= 80 AND score*1.2 < 90 THEN 'B'
        WHEN score*1.2 >= 60 AND score*1.2 < 80 THEN 'C'
        ELSE 'D' END
      WHEN age >= 20 AND age < 40 THEN 
        CASE 
        WHEN score*1.0 >= 90 THEN 'A'
        WHEN score*1.0 >= 80 AND score*1.0 < 90 THEN 'B'
        WHEN score*1.0 >= 60 AND score*1.0 < 80 THEN 'C'
        ELSE 'D' END
      ELSE 
        CASE 
        WHEN score*1.0 >= 90 THEN 'A'
        WHEN score*1.0 >= 80 AND score*1.0 < 90 THEN 'B'
        WHEN score*1.0 >= 60 AND score*1.0 < 80 THEN 'C'
        ELSE 'D' END
    END AS grades
FROM t_score_info
;

总结:

​ CASE WHEN 语句提供了一种在 SQL 查询中根据不同条件执行逻辑的灵活方法。它可用于简单的条件检查，也可用于复杂的逻辑操作。使用 CASE WHEN 可以使查询更具可读性，并且可以减少在应用程序代码中进行逻辑操作的需要。

end