高级子查询

第26章 高级子查询

selet deptno,max(sal) from emp group by deptno;
select ename,deptno,sal from emp where deptno=10 and sal=5000;
select ename,deptno,sal from emp where deptno=20 and sal=3000;
select ename,deptno,sal from emp where deptno=30 and sal=2850;

select ename,deptno,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

select ename,deptno,sal from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);

select ename,deptno,sal from emp e where sal in (select max(sal) from emp where deptno=e.deptno);

下面的两个为多表查询这样的话效率会更高
select ename from emp join (select deptno,max(sal) sal from emp group by deptno) using(deptno,sal);

select ename from emp join (select deptno,avg(sal) asal from emp group by deptno) a on emp.deptno=a.deptno and sal>asal;

求每个部门的员工的平均值
select ename,deptno,sal,(select avg(sal) from emp where deptno=e.deptno) asal from emp e;

select ename,emp.deptno,sal,asal from emp join (select deptno,avg(sal) asal from emp group by deptno) a on emp.deptno=a.deptno;

临时表的调用
with a as (select sal from emp)
select max(sal) from a
union all
select min(sal) from a
union all
select avg(sal) from a;
上面的语句只做了一次全表扫描，将数据放到了a这个临时表中，然后后面的语句都是直接调用临时表a中的数据