200字范文 > MySQL：查询每个部门的员工小时平均工资（显示部门名称部门员工小时平均工资）

MySQL：查询每个部门的员工小时平均工资（显示部门名称部门员工小时平均工资）

时间：2024-05-25 23:30:23

这里写自定义目录标题

介绍涉及的表的结构table departmenttable employeetable works_on思路建立大表（三表联合）一次查询临时表多次查询总结

介绍

涉及的表的结构

table department

用来表示部门信息的表，通过dno字段与其他表关联

mysql> select * from department;+---------------------------+-----+--------+--------------+| dname | dno | mgrssn | mgrstartdata |+---------------------------+-----+--------+--------------+| Economy Department | 000 | 09| -03-01 || Sales Department| 001 | 06| -06-01 || Human Resource Department | 010 | 12| -12-01 || Produce Department | 011 | 14| -05-01 || Research Department | 100 | 20| 2026-09-01 |+---------------------------+-----+--------+--------------+5 rows in set (0.05 sec)

table employee

用来表示雇员信息的表，与其他表通过dno字段关联

mysql> select * from employee;+--------+------+----------------------------------+---------+----------+-----+| ename | essn | address| salary | superssn | dno |+--------+------+----------------------------------+---------+----------+-----+| 白玉芬 | 00 | 广州市天河区珠江新城花城大道83号 | 1000.00 | 24 | 100 || 仓春莲 | 01 | 广东省广州市石牌西路68号 | 1100.00 | 20 | 100 || 仓红 | 02 | 广州市番禺区市桥清河东路41号| 1500.00 | 14 | 011 || 陈超云 | 03 | 佛山市汾江西路13号| 3000.00 | 02 | 011 || 陈高 | 04 | 佛山市南海区桂城天佑三路1号大院 | 4000.00 | 06 | 001 || 陈国祥 | 05 | 佛山市顺德区容奇大道东23号 | 3500.00 | 09 | 000 |......+--------+------+----------------------------------+---------+----------+-----+61 rows in set (0.34 sec)

table works_on

表示正在进行的项目的表，在此问题中通过dno与dapartment表相连

mysql> select * from works_on;+------+-----+-------+| essn | pno | hours |+------+-----+-------+| 00 | P1 |8 || 01 | P2 |3 || 02 | P4 |4 || 03 | P1 |1 || 03 | P3 |2 || 03 | P5 |1 |......+------+-----+-------+111 rows in set (19.70 sec)

思路

需要计算各部门的平均小时工资，就必须计算各部门的总工作时长和总工资。工作时长按照个人/项目/时长的样式存储在works_on表中，工资以个人/部门编号/工资的样式储存在employee表中，并且查询还需要显示部门名称。所以整个查询至少需要三表的联合查询，但怎样联合，有以下不同的思路。

建立大表（三表联合）一次查询

从速度优先的角度考虑，自然建立包含所有信息的大表查询最优先。

+---------------------------+-----+--------+--------------+--------+------+----------------------------------+---------+----------+-----+------+-----+-------+| dname | dno | mgrssn | mgrstartdata | ename | essn | address| salary | superssn | dno | essn | pno | hours |+---------------------------+-----+--------+--------------+--------+------+----------------------------------+---------+----------+-----+------+-----+-------+| Research Department | 100 | 20| 2026-09-01 | 白玉芬 | 00 | 广州市天河区珠江新城花城大道83号 | 1000.00 | 24 | 100 | 00 | P1 |8 || Research Department | 100 | 20| 2026-09-01 | 仓春莲 | 01 | 广东省广州市石牌西路68号 | 1100.00 | 20 | 100 | 01 | P2 |3 || Produce Department | 011 | 14| -05-01 | 仓红 | 02 | 广州市番禺区市桥清河东路41号| 1500.00 | 14 | 011 | 02 | P4 |4 || Produce Department | 011 | 14| -05-01 | 陈超云 | 03 | 佛山市汾江西路13号| 3000.00 | 02 | 011 | 03 | P1 |1 || Produce Department | 011 | 14| -05-01 | 陈超云 | 03 | 佛山市汾江西路13号| 3000.00 | 02 | 011 | 03 | P3 |2 |......+---------------------------+-----+--------+--------------+--------+------+----------------------------------+---------+----------+-----+------+-----+-------+111 rows in set (0.46 sec)

然后我们在这张表上用group by划分来计算各个部门的总工资和总工时。所以我们直接查询

mysql> SELECT department.dno,SUM(works_on.hours),SUM(employee.salary)-> from (department JOIN employee ON department.dno = employee.dno)JOIN works_on ON works_on.essn = employee.essn-> GROUP BY department.dno;+-----+---------------------+----------------------+| dno | SUM(works_on.hours) | SUM(employee.salary) |+-----+---------------------+----------------------+| 000 | 52 | 54900.00 || 001 | 58 | 90200.00 || 010 | 42 | 38900.00 || 011 | 121 | 132600.00 || 100 | 125 | 117800.00 |+-----+---------------------+----------------------+5 rows in set (0.08 sec)

这样可以么？

通过观察三联大表发现，同一个人可能出现多次。这是由于等值连接时，works_on表中一个人进行了多个项目。这时，一个人的工作时长得多次记录，但是工资却不行。

一次查询的思路大概行不通。

临时表多次查询

查询总工资

我们可以先查找一个包含各部门总工资的临时表，这个查询只需要employee一张表就可以实现：

mysql> select dno, SUM(employee.salary)from employeegroup by employee.dno;+-----+----------------------+| dno | SUM(employee.salary) |+-----+----------------------+| 000 | 31500.00 || 001 | 38600.00 || 010 | 29200.00 || 011 | 92000.00 || 100 | 59600.00 |+-----+----------------------+5 rows in set (0.12 sec)

查询总工时

然后查询一张包含各部门总工时的临时表，这个查询需要works_on和employee联合才可以实现

select dno, SUM(works_on.hours)from works_on join employee on works_on.essn = employee.essngroup by employee.dno;+-----+---------------------+| dno | SUM(works_on.hours) |+-----+---------------------+| 000 | 52 || 001 | 58 || 010 | 42 || 011 | 121 || 100 | 125 |+-----+---------------------+5 rows in set (0.08 sec)

联合两表计算平均工资

mysql> SELECT s_Hours.dno, s_Salary.s_s/s_Hours.s_hFROM ((SELECT employee.dno,SUM(works_on.hours) as s_hFROM employee JOIN works_on ON works_on.essn = employee.essnGROUP BY employee.dno) s_HoursJOIN(SELECT employee.dno,SUM(employee.salary) as s_sFROM employee JOIN department ON employee.dno = department.dnoGROUP BY employee.dno) s_SalaryON s_Salary.dno = s_Hours.dno);+-----+--------------------------+| dno | s_Salary.s_s/s_Hours.s_h |+-----+--------------------------+| 000 | 605.769231|| 001 | 665.517241|| 010 | 695.238095|| 011 | 760.330579|| 100 | 476.800000|+-----+--------------------------+5 rows in set (0.11 sec)