我的大學考試有兩個挑戰要做。第一個是Show data for employees (name, salary, and department) who are paid less than the average salary for their department.
我試過這個：
SELECT ename, sal, deptno
FROM Emp
WHERE sal < (SELECT AVG(sal) FROM EMP)
GROUP BY deptno;
但我得到了ORA-00979: not a GROUP BY expression錯誤。
另一個我沒有開始，但完全一樣， Show the data of employees (name, commission and department) who receive commission greater than the average commission in their department.
ps：我是從 SQL 開始的，所以這個話題還不是很深入。
uj5u.com熱心網友回復：
您可以為此使用視窗函式
SELECT ename, sal, deptno
FROM (
SELECT *,
AVG(sal) OVER (PARTITION BY deptno) AS AvgPerDept
FROM Emp
) AS Emp
WHERE sal < AvgPerDept
uj5u.com熱心網友回復：
您對第一個的嘗試不正確
該運算式SELECT AVG(sal) FROM EMP將給出每個人的平均工資，無論他們在哪個部門。
要獲得每個部門的平均值，您需要 GROUP BY dept_no
SELECT Emp.* , dept_salary.average_sal
FROM Emp
INNER JOIN
-- join with an average salary for each department
(
SELECT deptno, AVG(sal) as average_sal
FROM Emp
GROUP BY deptno
) dept_salary
ON Emp.deptno = dept_salary.deptno
WHERE Emp.dept_no = dept_salary.deptno AND Emp.sal < dept_salary.average_sal;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/336358.html
上一篇：從oracle表中洗掉特定行