备注:测试数据库版本为MySQL 8.0
如需要scott用户下建表及录入数据语句,可参考:
scott建表及录入数据sql脚本
一.需求
对来自多个行的数据进行计算。
为便于计算,可以把这些行转置到列中,这样所有需要的值都包含于1行中。
deptno 20 是总工资最高的部门,执行下列查询可以确认这一列:
mysql> select deptno, sum(sal) as sal
-> from emp
-> group by deptno;
+--------+----------+
| deptno | sal |
+--------+----------+
| 10 | 8750.00 |
| 20 | 10875.00 |
| 30 | 9400.00 |
+--------+----------+
3 rows in set (0.00 sec)
现在要计算deptno 20 和deptno 10之间以及deptno 20 和deptno 30 之间的总工资之差。
二.解决方案
使用聚集函数sum及case表达式转换总和,然后,在select列表中编写表达式:
select d20_sal - d10_sal as d20_10_diff,
d20_sal - d30_sal as d20_30_diff
from (
select sum(case when deptno = 10 then sal end) as d10_sal,
sum(case when deptno = 20 then sal end) as d20_sal,
sum(case when deptno = 30 then sal end) as d30_sal
from emp
) totals_by_dept;
测试记录:
mysql> select d20_sal - d10_sal as d20_10_diff,
-> d20_sal - d30_sal as d20_30_diff
-> from (
-> select sum(case when deptno = 10 then sal end) as d10_sal,
-> sum(case when deptno = 20 then sal end) as d20_sal,
-> sum(case when deptno = 30 then sal end) as d30_sal
-> from emp
-> ) totals_by_dept;
+-------------+-------------+
| d20_10_diff | d20_30_diff |
+-------------+-------------+
| 2125.00 | 1475.00 |
+-------------+-------------+
1 row in set (0.00 sec)
网友评论