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 查询中根据不同的条件返回特定的值。这可以帮助你根据数据库中的数据创建更复杂的查询和报告。