我正在嘗試獲取每個部門的平均員工工資。我期待兩行，因為一個部門沒有分配任何員工。
有人可以告訴我如何糾正這個問題。在此先感謝所有回答的人和您的專業知識。
以下是我的測驗案例和不準確的結果。
CREATE TABLE departments( department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 3, 'Sales' FROM DUAL UNION ALL
SELECT 2, 'DBA' FROM DUAL;
CREATE TABLE employees (employee_id, first_name, last_name, hire_date, salary, department_id) AS
SELECT 1, 'Lisa', 'Saladino', DATE '2001-04-03', 100000, 1 FROM DUAL UNION ALL
SELECT 2, 'Abby', 'Abbott', DATE '2001-04-04', 50000, 1 FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2001-04-05', 60000, 1 FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2001-04-06', 70000,1 FROM DUAL UNION ALL
SELECT 5, 'Vicky', 'Palazzo', DATE '2001-04-07', 88000,2 FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2001-04-08', 110000,1 FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Altman', DATE '2001-04-10', 66666, 1 FROM DUAL UNION ALL
SELECT 8, 'Jill', 'Coralnick', DATE '2001-04-11', 190000, 2 FROM DUAL UNION ALL
SELECT 9, 'Faith', 'Aaron', DATE '2001-04-17', 122000,2 FROM DUAL;
SELECT d.department_id,
d.department_name,
round(avg(e.salary) over (partition by e.department_id)) avg_sal
FROM departments d
JOIN employees e
ON (d.department_id = e.department_id)
DEPARTMENT_ID DEPARTMENT_NAME AVG_SAL
1 IT 76111
1 IT 76111
1 IT 76111
1 IT 76111
1 IT 76111
1 IT 76111
2 DBA 133333
2 DBA 133333
2 DBA 133333
uj5u.com熱心網友回復：
使用AVG聚合函式而不是分析函式并按部門的主鍵聚合：
SELECT d.department_id,
MAX(d.department_name) AS department_name,
ROUND(AVG(e.salary)) avg_sal
FROM departments d
INNER JOIN employees e
ON (d.department_id = e.department_id)
GROUP BY
d.department_id;
或者，您可以只聚合employees表并使用相關的子查詢來獲取名稱：
SELECT department_id,
( SELECT department_name
FROM departments d
WHERE d.department_id = e.department_id ) AS department_name,
ROUND(AVG(salary)) avg_sal
FROM employees e
GROUP BY
department_id;
小提琴
uj5u.com熱心網友回復：
您需要內部連接和avg聚合函式（不是決議形式！）：
SQL> SELECT d.department_id, d.department_name, ROUND (AVG (e.salary)) avg_sal
2 FROM departments d
3 JOIN employees e ON e.department_id = d.department_id
4 GROUP BY d.department_id, d.department_name;
DEPARTMENT_ID DEPAR AVG_SAL
------------- ----- ----------
1 IT 76111
2 DBA 133333
SQL>
如果您想顯示沒有員工的部門，您可以使用external join：
SQL> SELECT d.department_id, d.department_name, ROUND (AVG (e.salary)) avg_sal
2 FROM departments d
3 LEFT JOIN employees e ON e.department_id = d.department_id
4 GROUP BY d.department_id, d.department_name;
DEPARTMENT_ID DEPAR AVG_SAL
------------- ----- ----------
1 IT 76111
3 Sales
2 DBA 133333
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/531648.html
標籤：sql甲骨文平均