SQL Plus-具有每个部门id的工资的不同列

Question

我必须在department_id列中显示每个job_id的工资。

我老师的榜样榜样。

现在我已经这样做了，但我不知道如何只显示部门每一列的工资。

SELECT job_id, SUM(salary) "Dep20", SUM(salary) "Dep50", SUM(salary) "Dep60", SUM(salary) "Dep90", SUM(salary) "Total"
FROM employees
WHERE department_id IN ('20', '50', '60', '90')
GROUP BY job_id;

SQL Plus 中的结果

Answer 1

使用case表达式：

SELECT job_id,
SUM(CASE WHEN department_id = 20 THEN salary END) as Dep20,        
SUM(CASE WHEN department_id = 50 THEN salary END) as Dep50,        
SUM(CASE WHEN department_id = 60 THEN salary END) as Dep60,        
SUM(CASE WHEN department_id = 90 THEN salary END) as Dep90,
SUM(salary) as Total
FROM employees
WHERE department_id IN (20, 50, 60, 90)
GROUP BY job_id;

我猜测department_id是一个数字——这些值肯定是数字。数字应该和数字比较，而不是字符串，所以我去掉了单引号。

此外，不需要转义列别名，因此双引号是多余的。