GaussDB case when条件表达式用法

在 GaussDB 中，CASE WHEN 是一种条件表达式，用于根据不同的条件返回不同的值。它类似于编程语言中的 if-else 或 switch-case 逻辑，常用于 SQL 查询中对数据进行分类、转换或计算。以下是其用法的详细介绍：

1. 基本语法

GaussDB 支持两种 CASE WHEN 格式：

(1) 简单 CASE 表达式

CASE 列名  WHEN 条件值1 THEN 返回值1  WHEN 条件值2 THEN 返回值2     ...
 ELSE 默认值 END

特点：

• 直接对某一列的值进行匹配。
• 适用于简单的等值判断。

(2) 搜索 CASE 表达式

CASE  WHEN 条件1 THEN 返回值1  WHEN 条件2 THEN 返回值2     ...
 ELSE 默认值 END

特点：

• 支持更复杂的条件判断（如范围、逻辑运算符等）。
• 类似于编程中的 if-else if-else 结构。

2. 使用场景

(1) 数据分类

将某一列的值按规则分类：

SELECT     employee_name,
 CASE department  WHEN 'HR' THEN '人力资源部'  WHEN 'IT' THEN '技术部'  ELSE '其他部门'  END AS department\_category FROM employees;

(2) 动态计算字段

根据条件动态计算新字段：

SELECT     sales_amount,
 CASE  WHEN sales\_amount \> 10000 THEN '高销量'  WHEN sales\_amount BETWEEN 5000 AND 10000 THEN '中销量'  ELSE '低销量'  END AS sales\_level FROM sales;

(3) 用在存储过程中

--输入学号和科目名称，返回对应的平均成绩
create or replace procedure pro_avg_score(id int,coursename varchar(20),avgscore out int)
as
begin
    case when coursename='math' then select avg(math) into avgscore from student where student_id=id;
    when coursename='pysical' then select avg(pysical) into avgscore from student where student_id='id';
    when coursename='art' then select avg(art) into avgscore from student where student_id=id;
    when coursename='music' then select avg(music) avgscore from student where student_id=id;
    end case;
end;
/

--输出绩点
create or replace procedure pro_cal_point(id1 int,coursename varchar(30),point  out float)
as 
begin
    case when coursename='math' then select (case when math<=59 then 0
        when math<=69 then 0.1
        when math<=79 then 0.2
        when math<=89 then 0.3
        when math<=100 then 0.4 else 0.2 end) into point from student  where student_id=id1 limit 1;
    when coursename='pysical' then select (case when pysical<=59 then 0
        when pysical<=69 then 0.1
        when pysical<=79 then 0.2
        when pysical<=89 then 0.3
        when pysical<=100 then 0.4 else 0.2 end) into point from student  where student_id=id1 limit 1;
    when coursename='art' then select (case when art<=59 then 0
        when art<=69 then 0.1
        when art<=79 then 0.2
        when art<=89 then 0.3
        when art<=100 then 0.4 else 0.2 end) into point from student  where student_id=id1 limit 1;
    when coursename='music' then select (case when music<=59 then 0
        when music<=69 then 0.1
        when music<=79 then 0.2
        when music<=89 then 0.3
        when music<=100 then 0.4 else 0.2 end) into point from student  where student_id=id1 limit 1;
    else raise notice 'please input right course name;';
    end case;
end;
/
-- 编写函数，获取成绩绩点，
create or replace function fun_cal_point(id1 int,coursename varchar(30)) returns decimal(10,1) as $$
    declare point decimal(10,1);
    begin
        if coursename = 'math' then
            select (case when math <= 59 then 0
                when math <= 69 then 0.1
                when math <= 79 then 0.2
                when math <= 89 then 0.3
                when math <= 100 then 0.4
                else 0.2 end)
            into point from stu where id=id1;
        elsif coursename = 'art' then
            select (case when art <= 59 then 0
                when art <= 69 then 0.1
                when art <= 79 then 0.2
                when art <= 89 then 0.3
                when art <= 100 then 0.4
                else 0.2 end ) into point from stu where id=id1;
            elsif coursename = 'phy' then
                select (case when phy <= 59 then 0
                    when phy <= 69 then 0.1
                    when phy <= 79 then 0.2
                    when phy <= 89 then 0.3
                    when phy <= 100 then 0.4
                    else 0.2 end) into point from stu where id=id1;
                else raise notice 'please input right course name;';
                end if;
                return point;
        end;
        $$ language plpgsql;

3. 注意事项

1. END 关键字必须存在
   每个 CASE 表达式必须以 END 结尾，否则会报语法错误。

2. 条件顺序影响结果
   GaussDB 按顺序判断条件，一旦满足某个条件，后续条件将不再执行。因此，条件的排列需谨慎（尤其是重叠条件）。

3. ELSE 是可选的
   如果未提供 ELSE 且所有条件都不满足，CASE 表达式会返回 NULL。

4. 支持嵌套
   可以嵌套使用 CASE 表达式，但需注意逻辑复杂度：

   SELECT score,
   CASE WHEN score >= 90 THEN ‘A’ WHEN score >= 80 THEN ‘B’ ELSE CASE WHEN score >= 60 THEN ‘C’ ELSE ‘D’ END END AS grade FROM students;