Case When在SQL查询中的实际应用

CASE WHEN 是 SQL 查询中的一个条件表达式，它允许你根据一个或多个条件返回不同的值

以下是 CASE WHEN 在 SQL 查询中的一些实际应用：

1. 计算学生成绩等级：

SELECT
    student_id,
    student_name,
    score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM students;

2. 根据员工的工作年限分类：

SELECT
    employee_id,
    employee_name,
    years_of_experience,
    CASE
        WHEN years_of_experience < 3 THEN 'Junior'
        WHEN years_of_experience >= 3 AND years_of_experience < 5 THEN 'Intermediate'
        ELSE 'Senior'
    END AS experience_level
FROM employees;

3. 根据客户的消费金额分类：

SELECT
    customer_id,
    customer_name,
    total_spending,
    CASE
        WHEN total_spending >= 10000 THEN 'VIP'
        WHEN total_spending >= 5000 AND total_spending < 10000 THEN 'Regular'
        ELSE 'New Customer'
    END AS customer_type
FROM customers;

4. 根据订单状态显示不同的信息：

SELECT
    order_id,
    order_date,
    order_status,
    CASE
        WHEN order_status = 'Shipped' THEN 'Your order has been shipped.'
        WHEN order_status = 'Processing' THEN 'Your order is being processed.'
        WHEN order_status = 'Cancelled' THEN 'Your order has been cancelled.'
        ELSE 'Your order status is unknown.'
    END AS order_message
FROM orders;

这些示例展示了如何使用 CASE WHEN 在 SQL 查询中根据不同的条件返回特定的值。这可以帮助你根据数据库中的数据创建更复杂的查询和报告。