常用用法
在SELECT语句中,根据条件返回不同的值:
SELECT employee_id, salary, CASE WHEN salary < 20000 THEN 'Low' WHEN salary BETWEEN 20000 AND 40000 THEN 'Medium' ELSE 'High' END AS salary_category FROM employees;
聚合函数里的用法
在聚合函数中使用CASE表达式来计算基于条件的聚合值:
SELECT SUM(CASE WHEN department_id = 10 THEN salary ELSE 0 END) AS total_salary_department_10, SUM(CASE WHEN department_id = 20 THEN salary ELSE 0 END) AS total_salary_department_20 FROM employees;
在这个例子中,我们计算了部门10和部门20的总薪水。
GROUP BY子句里的用法
在GROUP BY子句中使用CASE表达式来对数据进行分组:
SELECT CASE WHEN job_id = 'SALESMAN' THEN 'Sales' WHEN job_id = 'MANAGER' THEN 'Management' ELSE 'Other' END AS job_category, COUNT(*) AS employee_count FROM employees GROUP BY CASE WHEN job_id = 'SALESMAN' THEN 'Sales' WHEN job_id = 'MANAGER' THEN 'Management' ELSE 'Other' END;
这里我们根据员工的职位将他们分类,并计算每个分类的员工数量。
CASE表达式嵌套
在CASE表达式中嵌套另一个CASE表达式:
SELECT employee_id, department_id, CASE WHEN department_id = 10 THEN CASE WHEN salary < 30000 THEN 'Low Pay' ELSE 'High Pay' END ELSE 'Other Department' END AS salary_status FROM employees;
在这个例子中,我们首先检查department_id
是否为10,如果是,再检查salary
是否低于30000,以确定薪水状态。
结合聚合函数和GROUP BY的复杂用法
结合聚合函数和GROUP BY子句,同时使用CASE表达式进行复杂的数据处理:
SELECT department_id, AVG(CASE WHEN job_id = 'SALESMAN' THEN salary ELSE NULL END) AS avg_salary_salesman, AVG(CASE WHEN job_id = 'MANAGER' THEN salary ELSE NULL END) AS avg_salary_manager FROM employees GROUP BY department_id;
在这个例子中,我们计算了每个部门的销售人员和经理的平均薪水。