查出每个部门大于平均工资的员工姓名，所在部门名称，工资的sql语句怎么写
发布网友
发布时间：2023-09-08 01:21
共3个回答
热心网友
时间：2023-10-02 08:43
每个部门大于所有人平均工资的员工：
Select BM,YG From TB WHERE Salary>(Select Avg(Salary) From TB)
每个部门大于所在部门所有人平均工资的员工：
Select BM,YG From TB
Join (Select BM,Avg(Salary) as AvgSalary From TB
Group By BM) As Temp
On TB.BM=Temp.BM
where Salary>Temp.AvgSalary追问我的意思是有两个表一个是emp员工表，一个是dept部门表，要查出每个部门大于所在部门所有人平均工资的员工的员工名称，部门名称，员工的工资
追答Select emp.Name,dept.Name,emp.Salary From emp
Join dept On emp.deptno=dept..deptno
Join (Select deptno,Avg(Salary) as AvgSalary From emp
Group By deptno) As Temp
On emp.deptno=Temp.deptno
And emp.Salary>Temp.AvgSalary
这样对不？
热心网友
时间：2023-10-02 08:44
from emp as a,
( select deptid,deptname ,avg(salary) c from dept group by deptid,deptname ) b
where a.deptid=b.deptid
and a.salary >b.c追问
你这样的语句结果不对，我是这样写的：select emp.ename,dept.dname,emp.sal
from emp, dept
where emp.deptno=b.deptno and emp.sal>(select deptno,avg(sal) from emp
group by deptno)
它提示错误是： 怎么样修改
group by deptno)
一个值大于两个数当然错啦
select a.empname, b.deptname ,a.salary ,b.salary
from emp a left join ( select deptid,deptname ,avg(salary) c from dept group by deptid,deptname ) b on a.deptid=b.deptid and a.salary >b.c
热心网友
时间：2023-10-02 08:44
FROM EMP,
DEPT,
(SELECT DEPT.DEPTNO AS D, AVG(EMP.SAL + NVL(EMP.COMM, 0)) ESC
FROM DEPT, EMP
WHERE EMP.DEPTNO = DEPT.DEPTNO
GROUP BY DEPT.DEPTNO) T
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.DEPTNO = T.D
AND (EMP.SAL + NVL(EMP.COMM, 0)) > T.ESC;
