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

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

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

计算学生成绩等级：

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 gradeFROM students;

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

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_levelFROM employees;

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

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_typeFROM customers;

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

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_messageFROM orders;

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