SQL 中 CASE WHEN 及 SELECT CASE WHEN 的用法详解_MsSqlserver

sql 中 case when 及 select case when 的用法

case when 是 sql 中非常实用的条件表达式，它允许你在查询中实现条件逻辑。以下是详细的用法说明：

1. 基本语法结构

case 
    when condition1 then result1
    when condition2 then result2
    ...
    else default_result
end

或者简单形式：

case expression 
    when value1 then result1
    when value2 then result2
    ...
    else default_result
end

2. 在 select 语句中的用法

示例1：简单条件判断

select 
    employee_name,
    salary,
    case 
        when salary > 10000 then '高薪'
        when salary > 5000 then '中等'
        else '一般'
    end as salary_level
from employees;

示例2：等值比较（简单形式）

select 
    product_name,
    case category_id
        when 1 then '电子产品'
        when 2 then '服装'
        when 3 then '食品'
        else '其他'
    end as category_name
from products;

3. 在 update 语句中的用法

update orders
set discount = 
    case 
        when order_amount > 1000 then 0.1
        when order_amount > 500 then 0.05
        else 0
    end;

4. 在 order by 中的用法

select * from customers
order by 
    case 
        when country = 'china' then 1
        when country = 'usa' then 2
        else 3
    end;

5. 在 group by 中的用法

select 
    case 
        when age < 20 then '青少年'
        when age between 20 and 30 then '青年'
        else '成年'
    end as age_group,
    count(*) as count
from users
group by age_group;

6. 嵌套 case when 用法

select 
    student_name,
    score,
    case 
        when score >= 90 then 'a'
        when score >= 80 then 
            case 
                when attendance_rate > 0.9 then 'b+'
                else 'b'
            end
        when score >= 70 then 'c'
        else 'd'
    end as grade
from students;

7. 在聚合函数中使用

select 
    department_id,
    sum(case when gender = 'm' then 1 else 0 end) as male_count,
    sum(case when gender = 'f' then 1 else 0 end) as female_count
from employees
group by department_id;