第26章、高级子查询案例

1.员工薪水高于平均的员工

<E1.select deptno,max(sal) from emp group by deptno;

<E2.select ename,deptno,sal from emp where deptno=10 and sal=5000;

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

<E4. select deptno,avg(sal) from emp group by deptno;

<E5.select ename,deptno,sal from emp where deptno=10 and sal>2916.66667

>>成对对比

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

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

//E8.select ename from emp join (select deptno,max(sal) sal from emp group by deptno) using (deptno,sal);

//E9.多次循环扫描操作

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

>>不成对对比

>>避免关联子查询,效率高

<E1.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;前后为分组语句，各扫描一次就好

<E2.select ename,deptno,sal,(select avg(sal) from emp where deptno=e.deptno) as from emp e;有多少行扫描多少次

>>关联update与delete

2.with子查询：能在同一查询块中，使用多个基于复合查询的select语句，返回查询的结果集并将其存储于用户的临时表空间，因此使用with子句提高性能。

3.求一个部门最高薪水，最低薪水，平均薪水，做三次全表扫描

<E1.select max(sal) from emp

union all

select min(sal) from emp

union all

select avg(sal) from emp;

<E2.使用with子查询提高效率，制作一次全表扫描，将扫描内容放入临时表a

>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;

<E3.select ename,deptno,sal,(select avg(sal) from emp where deptno=e.deptno) as from emp e;有多少行扫描多少次

改造：with a as ( select * from emp)

select ename,deptno,sal(select avg(sal) from a where deptno=e.deptno) asal from a e;