我有以下代碼，它可以找到每個部門的平均工資，并且運行良好。
我在將 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
uj5u.com熱心網友回復：
由于每個組中只有一個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_ID 部門名稱 AVERAGE_SALARY 2 銷售量 72500 1 它 82700
db<>在這里擺弄
uj5u.com熱心網友回復：
您可以使用這樣的查詢：
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 將員工加入部門。
當您進行分組時，請包括統計資訊中未包含的 2 個欄位。這將為您提供您正在尋找的 2 個欄位的統計資訊。
你的結果將是
DEPARTMENT_ID | 部門名稱 | AVERAGE_SALARY
------------: | :---------------- | -------------:
2 | 銷售 | 72500
1 | 資訊科技 | 82700
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/485854.html
上一篇：在SQL中使用左反連接
下一篇：獲取列的總和而不洗掉一些記錄