17,086
社区成员
发帖
与我相关
我的任务
分享
scott@YPCOST> select * from emp t where sal>(select avg(sal) from emp where t.deptno=deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 900 20
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 900 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 900 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
已选择6行。
已写入 file afiedt.buf
1 select * from emp,(select deptno,avg(sal) avgsal from emp group by deptno) t
2* where t.deptno=emp.deptno and emp.sal>t.avgsal
scott@YPCOST> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO AVGSAL
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ----
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 30 1566.66667
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 30 1566.66667
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 900 20 20 2175
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 900 20 20 2175
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 900 20 20 2175
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 10 2916.66667
已选择6行。
select * from emp a where a.sal>(select avg(sal) from emp b where a.deptno=b.deptno)
select *
from emp t
where sal>(select avg(sal) FROM emp where t.dep=dep)