Question

查出每个部门大于平均工资的员工姓名，所在部门名称，工资的sql语句怎么写

Answer 1

这问题有歧义，是要查出每个部门大于所有人平均工资的还是查出每个部门大于所在部门所有人平均工资？
每个部门大于所有人平均工资的员工：
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
这样对不？

Answer 2

select a.empname, b.deptname ,a.salary
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)

它提示错误是： 怎么样修改

追答

emp.sal>(select deptno,avg(sal) from emp
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

Answer 3

SELECT DEPT.DNAME, EMP.ENAME, (EMP.SAL + NVL(EMP.COMM, 0)) AS SC
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;