Oracle各部门平均工资

Question

我有下面的代码，它可以找到每个部门的平均工资，并且运行良好。

我在向输出添加department_name时遇到问题。我知道我必须加入表，但我无法让它工作。

任何帮助都会很感激。感谢所有回答的人。

CREATE table dept  (department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'SALES'  FROM DUAL;
CREATE TABLE employees (employee_id, manager_id, first_name, last_name, department_id, sal,
serial_number) AS
SELECT 1, NULL, 'Alice', 'Abbot', 1, 100000, 'D123' FROM DUAL UNION ALL
SELECT 2, 1, 'Beryl', 'Baron',1, 50000,'D124' FROM DUAL UNION ALL
SELECT 3, 1, 'Carol', 'Chang',1, 100000, 'A1424' FROM DUAL UNION ALL
SELECT 4, 2, 'Debra', 'Dunbar',1, 75000, 'A1425' FROM DUAL UNION ALL
SELECT 5, NULL, 'Emily', 'Eden',2, 90000, 'C1725' FROM DUAL UNION ALL
SELECT 6, 3, 'Fiona', 'Finn',1, 88500,'C1726' FROM DUAL UNION ALL
SELECT 7,5, 'Grace', 'Gelfenbein',2, 55000, 'C1727' FROM DUAL;

SELECT
department_id,
ROUND(AVG(sal), 2) AS AVERAGE_SALARY
FROM employees 
group by 
department_id;
Expected output
DEPARTMENT_ID DEPARTMENT_NAME AVERAGE_SALARY
1    IT         82700
2    SALES 72500

Answer 1

由于每个组中只有一个department_name，因此您可以连接表并在department_name上使用聚合函数:

SELECT e.department_id,
MAX(d.department_name) AS department_name,
ROUND(AVG(e.sal), 2) AS AVERAGE_SALARY
FROM   employees e
INNER JOIN dept d
ON (e.department_id = d.department_id)
GROUP BY e.department_id;

或者，可以先聚合后连接:

SELECT e.department_id,
d.department_name,
e.average_salary
FROM   (
SELECT department_id,
ROUND(AVG(sal), 2) AS average_salary
FROM   employees
GROUP BY department_id
) e
INNER JOIN dept d
ON (e.department_id = d.department_id);

对于您的示例数据，两者都输出:

DEPARTMENT_IDAVERAGE_SALARY7250082700

Answer 2

您可以使用这样的查询:

SELECT
d.department_id,
d.department_name,
ROUND(AVG(e.sal), 2) AS AVERAGE_SALARY
FROM employees e inner join dept d on e.department_id = d.department_id
group by 
d.department_id, d.department_name;

下面是一个例子:https://dbfiddle.uk/?rdbms=oracle_18&fiddle=156f4e503bc3d6bfed3009137acacb23

根据department_id加入department的员工。

分组时，包括统计数据中不包括的2个字段。这将为您提供您正在查找的两个字段的统计信息。

结果将是

DEPARTMENT_ID | DEPARTMENT_NAME | AVERAGE_SALARY------------: | -------------- | -------------:2 |销售| 725001 | IT | 82700