时间戳

首页 > 解决方案 > 如何从 sql 中的 2 个表中按组聚合和计算平均值？

sql - 如何从 sql 中的 2 个表中按组聚合和计算平均值？

问题描述

我有 2 张桌子，部门和员工。我需要获得部门名称和平均年龄。

create table Dep(name_dep char, id_dep int);

insert into Dep values("econ", 1);
insert into Dep values("credit", 2);
insert into Dep values("energy", 3);
insert into Dep values("retail", 4);
insert into Dep values("manufactury", 5);

create table Emp(id_emp int, id_dep int, age int, person_name char, salary int );
insert into Emp values(1, 1, 23, 'john', 200);
insert into Emp values(3, 2, 3, 'dalbai', 100);
insert into Emp values(6, 3, 53, 'borat', 300);
insert into Emp values(7, 1, 63, 'erjan', 1600);
insert into Emp values(9, 2, 73, 'sergey', 1000);
insert into Emp values(8, 5, 83, 'lucy', 20);
insert into Emp values(90, 4, 93, 'mike', 1200);

如何选择该部门员工的部门名称和平均年龄？

SELECT name_dep, average_age
FROM Emp e
INNER JOIN
(
    SELECT name_dep, AVG(age) AS average_age
    FROM Dep d
    GROUP BY id_dep
) d
    ON e.id_dep = d.id_dep

标签： sqlinner-joinaverageaggregate-functions

解决方案

加入表格，group by id_dep, name_dep然后得到平均年龄：

select
  d.name_dep, avg(age) average_age
from Dep d inner join Emp e 
on e.id_dep = d.id_dep
group by d.id_dep, d.name_dep

请参阅演示。
结果：

| name_dep    | average_age |
| ----------- | ----------- |
| econ        | 43          |
| credit      | 38          |
| energy      | 53          |
| retail      | 93          |
| manufactury | 83          |

推荐阅读