SQL CASE WHEN: How to Categorize Employee Salaries into Low, Medium, and High

91 SQL Challenge: Can You Use CASE WHEN to Group Employees by Salary? Imagine your manager asks you to divide employees into three groups based on their salary. Employees earning less than ₹40,000 should be in the Low category. Those earning between ₹40,000 and ₹80,000 should be in Medium. Anyone earning more than ₹80,000 should be in High. Sounds easy, right? But how would you write this using a SQL CASE expression? Let’s look at the data.

SQL 挑战 91：你能使用 CASE WHEN 按薪水对员工进行分组吗？想象一下，你的经理要求你根据薪水将员工分为三组。薪水低于 40,000 卢比的员工应归为“低”类别；薪水在 40,000 到 80,000 卢比之间的员工应归为“中”类别；薪水超过 80,000 卢比的员工应归为“高”类别。听起来很简单，对吧？但你会如何使用 SQL CASE 表达式来编写此查询呢？让我们看看数据。

employees emp_id| name| department| salary 101| Amit| IT| 35000 102| Priya| IT| 40000 103| Rahul| IT| 55000 104| Neha| HR| 80000 105| Sneha| HR| 85000 106| Kiran| HR| 95000 107| Meera| Finance| 75000 108| Arjun| Finance| 55000 Your challenge Display employee name, salary and salary category using CASE WHEN. Which query would you choose?

员工表 emp_id| name| department| salary 101| Amit| IT| 35000 102| Priya| IT| 40000 103| Rahul| IT| 55000 104| Neha| HR| 80000 105| Sneha| HR| 85000 106| Kiran| HR| 95000 107| Meera| Finance| 75000 108| Arjun| Finance| 55000 你的挑战：使用 CASE WHEN 显示员工姓名、薪水和薪水类别。你会选择哪个查询？

Option A SELECT name, salary, CASE WHEN salary < 40000 THEN ‘Low’ WHEN salary <= 80000 THEN ‘Medium’ ELSE ‘High’ END AS category FROM employees; Option B SELECT name, salary, CASE WHEN salary <= 40000 THEN ‘Low’ WHEN salary < 80000 THEN ‘Medium’ ELSE ‘High’ END AS category FROM employees; Option C SELECT name, salary, CASE WHEN salary < 40000 THEN ‘Low’ WHEN salary > 40000 THEN ‘Medium’ ELSE ‘High’ END AS category FROM employees;

选项 A：SELECT name, salary, CASE WHEN salary < 40000 THEN ‘Low’ WHEN salary <= 80000 THEN ‘Medium’ ELSE ‘High’ END AS category FROM employees; 选项 B：SELECT name, salary, CASE WHEN salary <= 40000 THEN ‘Low’ WHEN salary < 80000 THEN ‘Medium’ ELSE ‘High’ END AS category FROM employees; 选项 C：SELECT name, salary, CASE WHEN salary < 40000 THEN ‘Low’ WHEN salary > 40000 THEN ‘Medium’ ELSE ‘High’ END AS category FROM employees;

SQL Solution Option A is correct. SELECT name, salary, CASE WHEN salary < 40000 THEN ‘Low’ WHEN salary <= 80000 THEN ‘Medium’ ELSE ‘High’ END AS category FROM employees; Output name| salary| category Amit| 35000| Low Priya| 40000| Medium Rahul| 55000| Medium Neha| 80000| Medium Sneha| 85000| High Kiran| 95000| High Meera| 75000| Medium Arjun| 55000| Medium

SQL 解决方案：选项 A 是正确的。SELECT name, salary, CASE WHEN salary < 40000 THEN ‘Low’ WHEN salary <= 80000 THEN ‘Medium’ ELSE ‘High’ END AS category FROM employees; 输出结果：name| salary| category Amit| 35000| Low Priya| 40000| Medium Rahul| 55000| Medium Neha| 80000| Medium Sneha| 85000| High Kiran| 95000| High Meera| 75000| Medium Arjun| 55000| Medium

Why does this work? SQL checks each WHEN condition from top to bottom. If salary is less than 40000, the result is Low. If salary is up to 80000 and the first condition is not true, the result is Medium. If neither condition matches, ELSE returns High. One important thing to remember: CASE stops checking once it finds a matching condition. This is why the order of WHEN conditions matters.

为什么这样可行？SQL 会从上到下检查每个 WHEN 条件。如果薪水小于 40,000，结果为“低”。如果薪水最高为 80,000 且第一个条件不成立，结果为“中”。如果两个条件都不匹配，ELSE 将返回“高”。需要记住的一点是：一旦找到匹配的条件，CASE 就会停止检查。这就是为什么 WHEN 条件的顺序很重要。

Your turn Can you use CASE WHEN with GROUP BY to count how many employees belong to each salary category? Would you use CASE WHEN for similar business reports? Share your query in the comments.

轮到你了：你能使用 CASE WHEN 配合 GROUP BY 来统计每个薪水类别中有多少名员工吗？你会将 CASE WHEN 用于类似的业务报告吗？请在评论中分享你的查询。