SELECT * FROM emp t WHERE t.sal > (SELECT AVG(b.sal) FROM emp b WHERE t.deptno = b.deptno);
哈哈,莫笑我,这样其实效率没用表关联高.SELECT * FROM emp t,(SELECT deptno, AVG(sal) sal FROM emp group by deptno) b WHERE t.sal >b.sal and t.deptno=b.deptno ;
楼上的正解 不过也可以这么写 select * from scott.emp t1 where t1.sal > (select sal from (select deptno,avg(sal) sal from scott.emp group by deptno) t2 where t1.deptno = t2.deptno);
SELECT t.* FROM emp t,(SELECT deptno, AVG(sal) sal FROM emp group by deptno) b WHERE t.sal >b.sal and t.deptno=b.deptno
select * from emp a where a.sal>(select avg(sal) from emp b where a.deptno=b.deptno)
FROM emp t
WHERE t.sal > (SELECT AVG(b.sal) FROM emp b WHERE t.deptno = b.deptno);
FROM emp t,(SELECT deptno, AVG(sal) sal FROM emp group by deptno) b
WHERE t.sal >b.sal and t.deptno=b.deptno ;
不过也可以这么写
select * from scott.emp t1
where t1.sal > (select sal from
(select deptno,avg(sal) sal from scott.emp group by deptno) t2 where t1.deptno = t2.deptno);
FROM emp t,(SELECT deptno, AVG(sal) sal FROM emp group by deptno) b
WHERE t.sal >b.sal and t.deptno=b.deptno
select * from emp a
where a.sal>(select avg(sal) from emp b where a.deptno=b.deptno)