多表连接子查询学习笔记（msb16-23）

--每个部门平均薪水的薪水等级是多少。

要先求avg(sal)，再和另外一张表做连接。

select deptno,avg(sal) avg_sal from emp group by deptno;
将结果视作一张表。

select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno)t join salgrade s on(t.avg_sal between  s.losal and s.hisal);

 

--每个部门薪水的平均等级

先求每个人的薪水等级。

select deptno,ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);

看作一张新表，再按照deptno分组求平均：

select avg(grade),deptno from (select deptno,ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal)) t group by t.deptno;

 

--员工中哪些是经理人

雇员编号出现在别人的经理人中。

  select ename from emp where empno in(select mgr from emp);

更有效率的写法，使用distinct：

  select ename from emp where empno in(select distinct mgr from emp);

 

--求薪水最高值（不使用组函数）

考虑使用自连接，左边小于右边，则左边的最大值无法连接。

select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal);
不在里面的即为最大值。
select distinct sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));

 

--平均薪水最高的部门编号

select avg(sal)avg_sal,deptno from emp group by deptno;
看作一张表
select max(avg_sal) from(select avg(sal)avg_sal,deptno from emp group by deptno);
看作一个值
select deptno,avg_sal from (select avg(sal)avg_sal,deptno from emp group by deptno)where avg_sal=(select max(avg_sal) from(select avg(sal)avg_sal,deptno from emp group by deptno));

 

--平均薪水最高的部门编号的部门名称

select dname from dept where deptno=(select deptno from (select avg(sal)avg_sal,deptno from emp group by deptno)where avg_sal=(select max(avg_sal) from(select avg(sal)avg_sal,deptno from emp group by deptno)));

 

--求平均薪水的等级最低的部门的部门名称

分解法：
select dname from dept where deptno=(select deptno from (select avg(sal)avg_sal,deptno,grade from (select avg(sal)avg_sal,deptno from emp group by deptno)t join salgrade s on (t.avg_sal between s.losal and s.hisal) group by deptno)where grade=(select min(grade)from (select avg(sal)avg_sal,deptno,grade from (select avg(sal)avg_sal,deptno from emp group by deptno)t join salgrade s on (t.avg_sal between s.losal and s.hisal) group by deptno)));
平均薪水的等级最低的部门编号：
select deptno from (select avg(sal)avg_sal,deptno,grade from (select avg(sal)avg_sal,deptno from emp group by deptno)t join salgrade s on (t.avg_sal between s.losal and s.hisal) group by deptno)where grade=(select min(grade)from (select avg(sal)avg_sal,deptno,grade from (select avg(sal)avg_sal,deptno from emp group by deptno)t join salgrade s on (t.avg_sal between s.losal and s.hisal) group by deptno));
最低平均薪水等级（一个数）：
select min(grade)from (select avg(sal)avg_sal,deptno,grade from (select avg(sal)avg_sal,deptno from emp group by deptno)t join salgrade s on (t.avg_sal between s.losal and s.hisal) group by deptno);
平均薪水的等级（一张表）：
select avg(sal)avg_sal,deptno,grade from (select avg(sal)avg_sal,deptno from emp group by deptno)t join salgrade s on (t.avg_sal between s.losal and s.hisal) group by deptno;
平均薪水：
select avg(sal)avg_sal,deptno from emp group by deptno;

组函数可以嵌套，但不能超过两层。

 

--视图view

create view v$xxxxxx as (....)