查找职工信息,其工资超过他所在部门的平均工资.
select e1.EMPNO,e1.ENAME,e1.JOB,e1.MGR,e1.HIREDATE,e1.SAL,e1.COMM,e1.DEPTNO
from EMP e1
where e1.SAL>
(select AVG(SAL) from EMP e2
where e2.DEPTNO=e1.DEPTNO);
or
select e1.EMPNO,e1.ENAME,e1.JOB,e1.MGR,e1.HIREDATE,e1.SAL,e1.COMM,e1.DEPTNO
from EMP e1
where e1.SAL>
(select AVG(SAL) from EMP e2
where e2.DEPTNO=e1.DEPTNO
group by e2.DEPTNO);
select e1.EMPNO,e1.ENAME,e1.JOB,e1.MGR,e1.HIREDATE,e1.SAL,e1.COMM,e1.DEPTNO
from EMP e1
where e1.SAL>
(select AVG(SAL) from EMP e2
where e2.DEPTNO=e1.DEPTNO);
or
select e1.EMPNO,e1.ENAME,e1.JOB,e1.MGR,e1.HIREDATE,e1.SAL,e1.COMM,e1.DEPTNO
from EMP e1
where e1.SAL>
(select AVG(SAL) from EMP e2
where e2.DEPTNO=e1.DEPTNO
group by e2.DEPTNO);
楼主【tigoal】截止到2008-08-01 09:32:10的历史汇总数据(不包括此帖):
发帖的总数量:7 发帖的总分数:75 每贴平均分数:10
回帖的总数量:17 得分贴总数量:4 回帖的得分率:23%
结贴的总数量:6 结贴的总分数:75
无满意结贴数:0 无满意结贴分:0
未结的帖子数:1 未结的总分数:0
结贴的百分比:85.71 % 结分的百分比:100.00%
无满意结贴率:0.00 % 无满意结分率:0.00 %
楼主加油
取消马甲机器人,请点这里:http://www.java2000.net/mycsdn/robotStop.jsp?usern=tigoal
from EMP e1,(select AVG(SAL) SAL ,deptno from EMP groupby deptno) e2
where e1.SAL>e2.SAL and e1.deptno=e2.deptno